MySQL and Java developer's guide : Java open source library

* Shows Java developers everything they need to know to build Java database applications with MySQL. * Takes a hands-on, code-intensive approach in which readers will learn how to build a sophisticated Web database management application. * Begins with a review of the fundamentals of MySQL. * Explains using Java's JDBC with MySQL, as well as servlet and JSP programming with MySQL. * Provides a code-rich tutorial on how to build the sample Java database application using EJBs. * The companion Web site provides the full code examples plus links to useful sites. Knowledge iS POWER MySQL and Java Developer's Guide 1 Cover 1 Contents 4 Acknowledgments 12 About the Authors 14 Introduction 16 Chapter 1 An Overview of MySQL 24 Why Use an RDBMS? 25 Multiuser Access 25 Storage Transparency 25 Transactions 26 Searching, Modifying, and Analyzing Data 27 Ad Hoc Queries 28 Why Choose MySQL? 28 MySQL and JDBC 30 What's Next 31 Chapter 2 JDBC and Connector/J 32 What Is JDBC? 32 What about ODBC? 33 Modeling Database Applications with JDBC 34 JDBC Versions 36 JDBC Driver Types 36 SQL Standards 37 Examining the JDBC Interface 38 The java.sql Package 38 The javax.sql Package 41 Understanding Connector/J 44 JDBC Support within 3.0.1 45 Obtaining JDBC Drivers 47 What's Next 47 Chapter 3 Working with MySQL SQL 48 What Is a Database? 48 Database Models 50 Data Types 52 Designing a Database 52 Introducing MySQL SQL 55 Overview of MySQL 56 Creating Databases 57 Creating Tables 58 Inserts 62 Selects 63 SELECT Statement Extensions 65 Updates 70 Deletes 73 Using SHOW 74 More on Tables 76 Transactions 78 Functions/Operators 79 Joins 79 NULL 82 What's Next 82 Chapter 4 Installing MySQL, Java, and Connector/J 84 Installing MySQL 84 Linux Installation 85 Windows Installation 86 All Other Installations 86 Installing Java 87 Testing the Java Installation 87 Installing Connector/J 88 Testing the Connector/J Installation 89 What's Next 89 Chapter 5 Using JDBC with Java Applications and Applets 90 Hello World 90 Loading the Connector/J Driver 92 Using DriverManager to Connect to a Database 92 Executing Queries Through Statement Objects 98 Using the ResultSet Object 101 Determining the Cursor Position 102 Moving the Cursor 102 Getter Methods 103 Primitive Getters 105 Closing the Objects 108 Making It Real 108 Our Main Function 111 The init() Method 112 The buildGUI() Method 112 Executing a Query with No Results 114 Deleting Database Rows 120 Updating Database Rows 122 CREATE TABLE 124 DROP TABLE 124 Disconnecting from the Database 126 Advanced ResultSet Manipulation 127 One Step Forward 136 One Step Back 137 Fast-Forward to the End 137 Rewind to the Beginning 137 Goto Record 137 Freehand Query 138 Batches 138 Limiting Results 139 Database Warnings and Exceptions 140 What's Next 141 Chapter 6 Achieving Advanced Connector/J Functionality with Servlets 142 Servlets 142 DataSource Connections 145 Execution Environment 146 Databases 146 PreparedStatements 147 Connecting to the Database 152 Determining the Submit Type 152 Displaying Data 153 Updating Data 155 Using Placeholders in a Loop 156 Using Placeholders in PreparedStatement 157 Using setObject/setBytes 159 Getting BLOBs 162 Joins 164 Updatable ResultSets 165 The Update Button Code 172 The Insert Button Code 173 Update Methods 175 Manipulating Date/Time Types 177 Methods for Retrieving a Value as a Date Type 178 Methods for Retrieving a Value as a Time Type 178 Methods for Retrieving a Value as a Timestamp Type 178 Handling BLOB and CLOB 179 Using Streams to Pull Data 181 Handling ENUM 182 Using Connector/J with JavaScript 184 What's Next 186 Chapter 7 MySQL Type Mapping 188 Character Column Types 189 CHAR 189 VARCHAR 190 TINYTEXT 190 TEXT 190 MEDIUMTEXT 190 LONGTEXT 191 TINYBLOB 191 BLOB 191 MEDIUMBLOB 191 LONGBLOB 192 SET 192 ENUM 192 Using Character Types 192 Date and Time Column Types 194 DATE 195 TIME 195 DATETIME 195 YEAR 196 TIMESTAMP 196 Using Date and Time Types 196 Numeric Column Types 198 TINYINT 199 SMALLINT 199 MEDIUMINT 199 INT 200 BIGINT 200 FLOAT 200 DOUBLE 200 DECIMAL 201 Using Numeric Types 201 What's Next 203 Chapter 8 Transactions and Table Locking with Connector/J 204 Understanding the Problem 204 MySQL's Transaction Table Types 205 The InnoDB Table Type 205 The BDB Table Type 207 Converting to Transactional from Nontransactional 207 Performing Transactions in MySQL 208 Using the autocommit Variable 208 Update Transactions 210 The SELECT/INSERT Transaction 213 Multiple Table Transactions 214 Foreign Key Integrity on Deletes 215 Ending a Transaction 215 Transaction Isolation 215 Dirty Reads 216 Phantom Reads 217 Nonrepeatable Reads 217 Table Locking 218 What's Next 219 Chapter 9 Using Metadata 220 Using Database Metadata 220 Getting the Object 223 General Source Information 225 Feature Support 226 Data Source Limits 227 SQL Object Available 227 Transaction Support 227 The ResultSet Metadata 228 Getting Column Information 228 Other ResultSet Metadata 231 What's Next 233 Chapter 10 Connection Pooling with Connector/J 234 What Is a Connection Pool? 235 Pooling with DataSource 236 Pooling with the DriverManager 241 DDConnectionBroker 242 What's Next 244 Chapter 11 EJBs with MySQL 246 Multi-tier Architecture 246 Using Beans 248 EJB Types 248 The EJB Environment 249 Application Server Configuration 252 The Role of the Servlet 253 Entity Beans 253 Session Beans 257 Using the Beans 259 Adding a Query 261 Bean-Managed Persistence 263 ejbCreate() 264 ejbLoad() 265 ejbStore() 266 ejbRemove() 266 ejbFindByPrimaryKey() 267 Setter/Getter Methods 268 What's Next 268 Chapter 12 Building a General Interface for MySQL 270 Tasks 271 SQL Exceptions 275 MySQL Connections 276 The Task Delegate 278 The Task Manager 278 Task Results 287 The Database Information Task 291 User Input for Tasks 293 The SQL Query Task 295 The Show Columns Task 298 The Insert Row Task 303 What's Next 309 Chapter 13 Database Administration 310 Using the mysql Administration Application 310 Managing Users and Permissions 312 Changing Root 312 Adding Users 313 Limiting Resources 315 Configuring the Query Cache 316 Forcing a Cache 317 Understanding Log Files 317 Error Logs 318 General Logs 318 Binary Logs 319 Slow Query Logs 319 Maintaining Your Tables 319 Repairing Tables 320 Backing Up and Restoring Your Database 321 Restoring Data 324 InnoDB Table Types 325 DBD Table Types 325 What's Next 326 Chapter 14 Performance and Tuning 328 Connector/J 3.0 Performance 328 Database Tuning 331 Server Options 331 Using RAID 332 Optimizing Tables 332 The MySQL Query Optimizer 333 Table Indexes 335 JDBC Tuning 336 Minimizing Data Requests 336 Keeping Consistent Connections 337 Handling Statements 338 Batching 339 Using Transactions and Locking 339 Defining the Architecture 340 Getting Data 340 Conclusion 341 Appendix A MySQL Development and Test Environments 342 Test Architecture #1 342 Test Architecture #2 343 Servlet Architecture 344 The EJB Architecture 346 Appendix B Databases and Tables 348 The accounts Database and Tables 348 The identification Database and Tables 349 Test Databases 350 Database Products 350 The Database Test 350 Appendix C The JDBC API and Connector/J 352 The java.sql Package 353 Array 354 BatchUpdateException 355 Blob 355 CallableStatement 356 Clob 358 Connection 358 DataTruncation 360 DatabaseMetaData 360 Date 366 Driver 366 DriverManager 366 DriverPropertyInfo 367 ParameterMetaData 367 PreparedStatement 368 Ref 369 ResultSet 370 ResultSetMetaData 373 Savepoint 374 SQLData 374 SQLException 375 SQLInput 375 SQLOutput 376 SQLPermission 376 SQLWarning 377 Statement 377 Struct 378 Time 379 Timestamp 379 Types 380 The javax.sql Package 381 ConnectionEvent 382 ConnectionEventListener 382 ConnectionPoolDataSource 382 DataSource 383 PooledConnection 383 RowSet 383 RowSetEvent 385 RowSetInternal 385 RowSetListener 386 RowSetMetaData 386 RowSetReader 386 RowSetWriter 387 XAConnection 387 XADataSource 387 Appendix D MySQL Functions and Operators 390 Arithmetic Functions/Operators 392 Comparison Functions/Operators 395 Logical Operators 398 Control Functions 400 String Functions/Operators 402 Grouping Functions 407 Date and Time Functions 409 Other Functions 417 Appendix E Connector/J Late-Breaking Additions 420 Failover Support 420 Windows Named Pipes 421 Batch Processing Error Continuation 421 Strict Updates 422 Profile SQL 422 SSL 422 Index 424

The first comprehensive guide to MySQL for Java developers

With more than three million users, MySQL is the most popular open source database server in the world. It is the most popular database server for Linux and also runs on Windows, Macintosh, FreeBSD, and many flavors of UNIX. Most developers have built MySQL applications using programming languages like C, PHP, and Perl, but three years ago the author of this book, Mark Matthews, created MM.MySQL, the Java driver for MySQL. Since then, MySQL AB has shown their support for Java developers by hiring Mark, adopting his driver, and re-releasing it as Connector/J. Now, Mark Matthews and team explain everything you need to build database applications with MySQL and Java.

This book is a complete and authoritative tutorial and reference on MySQL for Java and takes a hands-on, code-intensive approach so you can learn how to build sophisticated Web database applications. Matthews begins by reviewing the fundamentals of MySQL 3.x and 4.x, followed by:

• Details of how to use JDBC with MySQL for Java application, servlet, JSP, and EJB development
• Several sample database applications, including an Address and Fingerprint Management system that uses servlets, and an Advanced Account Management system that uses EJBs
• MySQL and JDBC tuning techniques
• MySQL administration advice
• JDBC API reference specific to MySQL and Connector/J

Hier erfahren Java Entwickler alles, was sie wissen mssen, um Java Datenbankanwendungen mit MySQL zu entwickeln. "MySQL and Java Developer's Guide" ist der erste umfassende Leitfaden zu MySQL fr Java-Entwickler. Er basiert auf einem praxisorientierten und codeintensiven Ansatz. Hier lernen Java Entwickler, wie sie eine komplexe Managementanwendung fr Webdatenbanken entwickeln. Zunchst gibt der Autor einen berblick ber die Grundlagen von MySQL. Dann erklrt er, wie man JDBC mit MySQL verwendet und wie man Servlets und JSP mit MySQL entwickelt. Mit einem codeintensiven Tutorial. Es demonstriert, wie man die Muster-Datenbankanwendung mit Hilfe von EJB entwickelt. Mit begleitender Website. Sie enthlt die kompletten Quellcodebeispiele aus dem Buch sowie Links zu anderen ntzlichen Seiten. Geschrieben von Mark Matthews, dem Erfinder des JDBC-Treibers fr MySQL und unbestrittene Autoritt auf dem Gebiet der Java Programmierung mit MySQL.