MySQL and Java Developer’s Guide - Mark Matthews, Jim Cole, Joseph D. Gradecki

MySQL and Java Developer’s Guide - Mark Matthews, Jim Cole, Joseph D. Gradecki - 2003.
   This book is written for Java developers who need to interface their code to a back-end database. The book’s specifics deal with MySQL and Connector/J, but this doesn’t limit the information because JDBC is designed to be portable against many databases. If you aren’t using MySQL, you still find valuable information.


Contents
Acknowledgments xi
About the Authors xiii
Introduction xv
Chapter 1 An Overview of MySQL 1
Why Use an RDBMS? 2
Multiuser Access 2
Storage Transparency 2
Transactions 3
Searching, Modifying, and Analyzing Data 4
Ad Hoc Queries 5
Why Choose MySQL? 5
MySQL and JDBC 7
What’s Next 8
Chapter 2 JDBC and Connector/J 9
What Is JDBC? 9
What about ODBC? 10
Modeling Database Applications with JDBC 11
JDBC Versions 13
JDBC Driver Types 13
SQL Standards 14
Examining the JDBC Interface 15
The java.sql Package 15
The javax.sql Package 18
Understanding Connector/J 21
JDBC Support within 3.0.1 22
Obtaining JDBC Drivers 24
What’s Next 24
Chapter 3 Working with MySQL SQL 25
What Is a Database? 25
Database Models 27
Data Types 29
Designing a Database 29
Introducing MySQL SQL 32
Overview of MySQL 33
Creating Databases 34
Creating Tables 35
Inserts 39
Selects 40
SELECT Statement Extensions 42
Updates 47
Deletes 50
Using SHOW 51
More on Tables 53
Transactions 55
Functions/Operators 56
Joins 56
NULL 59
What’s Next 59
Chapter 4 Installing MySQL, Java, and Connector/J 61
Installing MySQL 61
Linux Installation 62
Windows Installation 63
All Other Installations 63
Installing Java 64
Testing the Java Installation 64
Installing Connector/J 65
Testing the Connector/J Installation 66
What’s Next 66
Chapter 5 Using JDBC with Java Applications and Applets 67
Hello World 67
Loading the Connector/J Driver 69
Using DriverManager to Connect to a Database 69
Executing Queries Through Statement Objects 75
Using the ResultSet Object 78
Determining the Cursor Position 79
Moving the Cursor 79
Getter Methods 80
Primitive Getters 82
Closing the Objects 85
Making It Real 85
Our Main Function 88
The init() Method 89
The buildGUI() Method 89
Executing a Query with No Results 91
Deleting Database Rows 97
Updating Database Rows 99
CREATE TABLE 101
DROP TABLE 101
Disconnecting from the Database 103
Advanced ResultSet Manipulation 104
One Step Forward 113
One Step Back 114
Fast-Forward to the End 114
Rewind to the Beginning 114
Goto Record 114
Freehand Query 115
Batches 115
Limiting Results 116
Database Warnings and Exceptions 117
What’s Next 118
Chapter 6 Achieving Advanced Connector/J Functionality with Servlets 119
Servlets 119
DataSource Connections 122
Execution Environment 123
Databases 123
PreparedStatements 124
Connecting to the Database 129
Determining the Submit Type 129
Displaying Data 130
Updating Data 132
Using Placeholders in a Loop 133
Using Placeholders in PreparedStatement 134
Using setObject/setBytes 136
Getting BLOBs 139
Joins 141
Updatable ResultSets 142
The Update Button Code 149
The Insert Button Code 150
Update Methods 152
Manipulating Date/Time Types 154
Methods for Retrieving a Value as a Date Type 155
Methods for Retrieving a Value as a Time Type 155
Methods for Retrieving a Value as a Timestamp Type 155
Handling BLOB and CLOB 156
Using Streams to Pull Data 158
Handling ENUM 159
Using Connector/J with JavaScript 161
What’s Next 163
Chapter 7 MySQL Type Mapping 165
Character Column Types 166
CHAR 166
VARCHAR 167
TINYTEXT 167
TEXT 167
MEDIUMTEXT 167
LONGTEXT 168
TINYBLOB 168
BLOB 168
MEDIUMBLOB 168
LONGBLOB 169
SET 169
ENUM 169
Using Character Types 169
Date and Time Column Types 171
DATE 172
TIME 172
DATETIME 172
YEAR 173
TIMESTAMP 173
Using Date and Time Types 173
Numeric Column Types 175
TINYINT 176
SMALLINT 176
MEDIUMINT 176
INT 177
BIGINT 177
FLOAT 177
DOUBLE 177
DECIMAL 178
Using Numeric Types 178
What’s Next 180
Chapter 8 Transactions and Table Locking with Connector/J 181
Understanding the Problem 181
MySQL's Transaction Table Types 182
The InnoDB Table Type 182
The BDB Table Type 184
Converting to Transactional from Nontransactional 184
Performing Transactions in MySQL 185
Using the autocommit Variable 185
Update Transactions 187
The SELECT/INSERT Transaction 190
Multiple Table Transactions 191
Foreign Key Integrity on Deletes 192
Ending a Transaction 192
Transaction Isolation 192
Dirty Reads 193
Phantom Reads 194
Nonrepeatable Reads 194
Table Locking 195
What’s Next 196
Chapter 9 Using Metadata 197
Using Database Metadata 197
Getting the Object 200
General Source Information 202
Feature Support 203
Data Source Limits 204
SQL Object Available 204
Transaction Support 204
The ResultSet Metadata 205
Getting Column Information 205
Other ResultSet Metadata 208
What’s Next 210
Chapter 10 Connection Pooling with Connector/J 211
What Is a Connection Pool? 212
Pooling with DataSource 213
Pooling with the DriverManager 218
DDConnectionBroker 219
What’s Next 221
Chapter 11 EJBs with MySQL 223
Multi-tier Architecture 223
Using Beans 225
EJB Types 225
The EJB Environment 226
Application Server Configuration 229
The Role of the Servlet 230
Entity Beans 230
Session Beans 234
Using the Beans 236
Adding a Query 238
Bean-Managed Persistence 240
ejbCreate() 241
ejbLoad() 242
ejbStore() 243
ejbRemove() 243
ejbFindByPrimaryKey() 244
Setter/Getter Methods 245
What’s Next 245
Chapter 12 Building a General Interface for MySQL 247
Tasks 248
SQL Exceptions 252
MySQL Connections 253
The Task Delegate 255
The Task Manager 255
Task Results 264
The Database Information Task 268
User Input for Tasks 270
The SQL Query Task 272
The Show Columns Task 275
The Insert Row Task 280
What’s Next 286
Chapter 13 Database Administration 287
Using the mysql Administration Application 287
Managing Users and Permissions 289
Changing Root 289
Adding Users 290
Limiting Resources 292
Configuring the Query Cache 293
Forcing a Cache 294
Understanding Log Files 294
Error Logs 295
General Logs 295
Binary Logs 296
Slow Query Logs 296
Maintaining Your Tables 296
Repairing Tables 297
Backing Up and Restoring Your Database 298
Restoring Data 301
InnoDB Table Types 302
DBD Table Types 302
What’s Next 303
Chapter 14 Performance and Tuning 305
Connector/J 3.0 Performance 305
Database Tuning 308
Server Options 308
Using RAID 309
Optimizing Tables 309
The MySQL Query Optimizer 310
Table Indexes 312
JDBC Tuning 313
Minimizing Data Requests 313
Keeping Consistent Connections 314
Handling Statements 315
Batching 316
Using Transactions and Locking 316
Defining the Architecture 317
Getting Data 317
Conclusion 318
Appendix A MySQL Development and Test Environments 319
Test Architecture #1 319
Test Architecture #2 320
Servlet Architecture 321
The EJB Architecture 323
Appendix B Databases and Tables 325
The accounts Database and Tables 325
The identification Database and Tables 326
Test Databases 327
Database Products 327
The Database Test 327
Appendix C The JDBC API and Connector/J 329
The java.sql Package 330
Array 331
BatchUpdateException 332
Blob 332
CallableStatement 333
Clob 335
Connection 335
DataTruncation 337
DatabaseMetaData 337
Date 343
Driver 343
DriverManager 343
DriverPropertyInfo 344
ParameterMetaData 344
PreparedStatement 345
Ref 346
ResultSet 347
ResultSetMetaData 350
Savepoint 351
SQLData 351
SQLException 352
SQLInput 352
SQLOutput 353
SQLPermission 353
SQLWarning 354
Statement 354
Struct 355
Time 356
Timestamp 356
Types 357
Contents ix
The javax.sql Package 358
ConnectionEvent 359
ConnectionEventListener 359
ConnectionPoolDataSource 359
DataSource 360
PooledConnection 360
RowSet 360
RowSetEvent 362
RowSetInternal 362
RowSetListener 363
RowSetMetaData 363
RowSetReader 363
RowSetWriter 364
XAConnection 364
XADataSource 364
Appendix D MySQL Functions and Operators 367
Arithmetic Functions/Operators 369
Comparison Functions/Operators 372
Logical Operators 375
Control Functions 377
String Functions/Operators 379
Grouping Functions 384
Date and Time Functions 386
Other Functions 394
Appendix E Connector/J Late-Breaking Additions 397
Failover Support 397
Windows Named Pipes 398
Batch Processing Error Continuation 398
Strict Updates 399
Profile SQL 399
SSL 399
Index 401