Java Operation MySQL Database

Database operations in Java project development is a very important aspect, for beginners, MySQL is a relatively easy to understand a common database, this article records how to use Java to operate the MySQL database.

Chapter I the concept of JDBC

JDBC (Java database Connectivity) is the link between Java and the database, in short, Java using the JDBC API to shield out the underlying database implementation of the differences. Java works differently for different databases, with two advantages:

• Simplifies program code access to the database without requiring details related to the underlying database implementation
• Multiple databases can be used in the same Java program

For different databases, load different drivers, for example, for MySQL database, you need to add Externel Lib (as shown in 1.1) in the project, add the LOAD statement class.forname ("Com.mysql.jdbc.Driver") in the program; Can.

Figure 1.1

The JDBC API is fixed, developed by Sun, and all third-party database vendor databases are provided to Java access and must implement this API, which we call the JDBC driver. The JDBC Driver Manager (Java.sql.DriverManager) class is also implemented by Sun, which is responsible for registering a specific JDBC driver to establish a database connection based on the JDBC driver for a particular database. For example, for MySQL, a typical registration statement is: Drivermanager.registerdriver (new Com.mysql.jdbc.Driver ());

Chapter Two An example of a Java connection to a MySQL database

The JDBC API is fixed, developed by Sun, and all third-party database vendor databases are provided to Java access and must implement this API, which we call the JDBC driver. The JDBC Driver Manager (Java.sql.DriverManager) class is also implemented by Sun, which is responsible for registering a specific JDBC driver to establish a database connection based on the JDBC driver for a particular database. For example, for MySQL, a typical registration statement is: Drivermanager.registerdriver (new Com.mysql.jdbc.Driver ());

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

package databasetest;import java.sql.*; import java.util.*; import java.io.*; public class JDBCTest {     public static void main(String[] args) throws ClassNotFoundException,SQLException{         // TODO Auto-generated method stub         Connection con;           Statement stmt;           ResultSet rs;         //加载驱动程序，下面的代码加载MySQL驱动程序           Class.forName("com.mysql.jdbc.Driver");           String dbUrl =  "jdbc:mysql://localhost:3306/BookDB?useUnicode=true&characterEncoding=UTF8";           String dbUser="root";           String dbPwd="miwusenlin";         //注册MySQL驱动程序           DriverManager.registerDriver(newcom.mysql.jdbc.Driver());         //用适当的驱动程序连接到数据库，建立数据库连接           con = java.sql.DriverManager.getConnection(dbUrl,dbUser,dbPwd);         //创建一个SQL声明           stmt = con.createStatement();         //增加新记录           stmt.executeQuery("SET NAMES UTF8");           stmt.executeUpdate("insert into BOOKS (ID,NAME,TITLE,PRICE) values(‘999‘,‘汤姆‘,‘Tomcat Bible‘,44.5)");                                                                 //查询记录           rs = stmt.executeQuery("select ID,NAME,TITLE,PRICE from BOOKS");         //打印所显示的数据           while (rs.next()){             String col1 = rs.getString(1);             String col2 = rs.getString(2);             String col3 = rs.getString(3);             float col4 = rs.getFloat(4);           //输出查询结果             System.out.println(col1+" "+col2+" "+col3+" "+col4 );           }           stmt.executeUpdate("delete from BOOKS where ID=‘999‘");                   //关闭数据库连接           rs.close();           stmt.close();           con.close();     }}

In the above code, it is important to note that: String Dburl = "Jdbc:mysql://localhost:3306/bookdb?useunicode=true&characterencoding=utf8"; The character encoding is set to the same format as the table in the database.

Chapter III Interfaces and classes in the java.sql package

The JDBC API for the MySQL database is primarily located in the java.sql package, where the key interfaces are:

• DriverManager class: Mainly used to register the database driver
• Connection interface: Represents a database connection
• Statement interface: Responsible for executing SQL statements
• PreparedStatement interface: The SQL statement that is responsible for executing the pre-prepared
• CallableStatement interface: Responsible for executing SQL stored procedures
• ResultSet interface: Represents the return value of a SQL query result

Reference:

Wikipedia: Java database connection

java.sql Package Official documentation

Java Database Connection Pool