Introduction to JDBC and Examples

Introduction to JDBC and Examples
1. What is JDBC? Jdbc api is a Java API that can access table data of any type, especially data stored in relational databases. JDBC represents a Java database connection.
The API tasks contained in the JDBC library are usually used with the database:

Connect to database

Create an SQL or MySQL statement

Execute SQL or MySQL queries in the database

View and modify records

  2. What are the conditions for using JDBC?Address of the target database host database software the port number occupied by the host to log on to the database using the user name and password of the user name to connect to the database

 

Iii. Principles of JDBC technology

 

We know that there are various types of databases. The standards and specifications of databases produced by different manufacturers are different. At this time, if we use JAVA code to send SQL statements, it is necessary to write one set of operation code based on different databases, which costs a lot for program developers. Therefore, when SUN develops JDBC technology, A set of standard interfaces are defined, and database developers must provide a driver to implement this interface. As long as the program developer uses the database driver during development, it is developed in a consistent way, without having to write a set of code to adapt to different databases.

Iv. Core APIs in JDBC

|-Driver: The interface implemented by the Driver class.

|-Connection connect (String url, Properties info) -- used to connect to the database and obtain the Connection object.

Parameters to be set in Properties:

Url: the URL string of the database connection. Protocol + database sub-Protocol + host + port + database

User: Database user Name

Password: the user's password.

|-Connection: interface for connecting to the database

|-Statement createStatement () -- creates a Statement object to send SQL statements.

|-PreparedStatement prepareStatement (String SQL)-create a PreparedStatement object to send pre-compiled SQL statements

|-CallableStatement prepareCall (String SQL) -- creates a CallableStatement object to call the stored procedure.

|-Statement: used to execute static SQL statements

|-Int executeUpdate (String SQL) -- execute the update operation (DDL + DML)

|-ResultSet executeQuery (String SQL) -- execute the query operation (DQL)

|-PreparedStatement: used to execute pre-compiled SQL statements

|-Int executeUpdate () -- execute the update operation

|-ResultSet executeQuery () -- execute the query operation

|-CallableStatement: SQL statement used to execute the Stored Procedure

|-ResultSet executeQuery () -- call the Stored Procedure

|-ResultSet: result set. Used to encapsulate the queried data of the database

|-Boolean next () -- move the record cursor to the next row

|-Object getObject (int columnIndex) -- Obtain the field value.

 

5. Create a JDBC application Step 1. Import the package

This requires that you have a software package containing the JDBC class required for database programming. In most cases, using import java. SQL. * is sufficient, as shown below:

 

import java.sql.*;

2. register the jdbc driver

 

You need to initialize the driver so that you can open the communication channel with the database. The following are code snippets to achieve this goal:

 

Class.forName("com.mysql.jdbc.Driver");

3. Open a connection

 

This requires the DriverManager. getConnection () method to create a Connection object, which represents a database with a physical Connection, as shown below:

 static final String USER = "username"; static final String PASS = "password"; System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS);

4. Execute a query

 System.out.println("Creating statement...");stmt = conn.createStatement();String sql;sql = "SELECT id, first, last, age FROM Employees";ResultSet rs = stmt.executeQuery(sql);

If there is an SQL UPDATE, INSERT, or DELETE statement, the following code snippet is required:

 

System.out.println("Creating statement...");stmt = conn.createStatement();String sql;sql = "DELETE FROM Employees";ResultSet rs = stmt.executeUpdate(sql);

 

5. extract data from the results

This step is required to obtain data from the database. You can use the appropriate ResultSet. getXXX () method to retrieve the following data results:

 

while(rs.next()){    //Retrieve by column name    int id  = rs.getInt("id");    int age = rs.getInt("age");    String first = rs.getString("first");    String last = rs.getString("last");    //Display values    System.out.print("ID: " + id);    System.out.print(", Age: " + age);    System.out.print(", First: " + first);    System.out.println(", Last: " + last);}

 

6. Clean up the environment

All database resources should be explicitly disabled. The JVM-dependent garbage collection is as follows:

 

rs.close();stmt.close();conn.close();

 

Vi. Examples

The following example shows how to create, insert, and search for data tables:

The project directory is as follows:

Remember to add the corresponding package: mysql-connector-java-5.1.22-bin.jar

 

Package com. mucfc; import java. SQL. *; public class JdbcTest {// defines the database driver private static final String DBDRIVER = "com. mysql. jdbc. driver "; // database connection address private static final String DBURL =" jdbc: mysql: // localhost: 3306/school "; // school indicates the database username private static final String DBUSER = "root"; // the password of the database on the computer private static final String DBPASS = "christmas258 @"; public void testDDL () {try {// 1. register the driver Class. forName (DBDRIVER );/ /2. obtain Connection conn = DriverManager. getConnection (DBURL, DBUSER, DBPASS); // 3. create Statement object Statement stmt = conn. createStatement (); // 4. prepare the SQL statement String SQL = "CREATE TABLE student (sid INT PRIMARY KEY, sname VARCHAR (20), age INT)"; // 5. use the statement object to send an SQL statement and return the execution result int count = stmt.exe cuteUpdate (SQL); System. out. println ("create table student ...... "); // 6. print the execution result System. out. println ("affected" + count + "records ");/ /Execute the insert operation System. out. println ("Inserting records into the table... "); SQL =" insert into student "+" VALUES (100, 'xiaowen ', 18) "; stmt.exe cuteUpdate (SQL ); SQL = "insert into student" + "VALUES (101, 'clerk', 25)"; stmt.exe cuteUpdate (SQL); SQL = "INSERT INTO student" + "VALUES (102, 'White ', 30) "; stmt.exe cuteUpdate (SQL); SQL =" INSERT INTO student "+" VALUES (103, '小', 28 )"; stmt.exe cuteUpdate (SQL); System. Out. println ("Inserted records into the table... "); // execute the query SQL =" SELECT * FROM student "; System. out. println ("SELECT records FROM the table... "); ResultSet rs = stmt.exe cuteQuery (SQL); // output the query result while (rs. next () {// first obtain the data int sid = rs. getInt ("sid"); String sname = rs. getString ("sname"); int age = rs. getInt ("age"); // print the result System. out. print ("sid:" + sid); System. out. print ("sname:" + sname); System. o Ut. println ("age:" + age) ;}rs. close (); // 7. close the resource try {if (stmt! = Null) {stmt. close () ;}catch (Exception e) {e. printStackTrace () ;}try {if (conn! = Null) {conn. close () ;}} catch (Exception e) {}} catch (Exception e) {e. printStackTrace () ;}} public static void main (String [] args) {JdbcTest jdbcTest = new JdbcTest (); jdbcTest. testDDL ();}}

Run the command directly. The output result is as follows:

 

 

From the above example, we can see that this process is still relatively simple. Here we should note that the school database should exist. If not, create one in MySql using the following statement:

 

CREATE DATABASE school;

If you want to perform DQL and DDL operations, you can write the SQL statement and then call the executlUpdate method of statement to execute the SQL statement for the database. This method returns an integer, indicates how many rows in the database are affected. If we do not change the above program and want to issue an SQL statement to the database, we need to write a program to connect again. After the operation, we need to close the statement object and connection object, this is very cumbersome. Therefore, we usually extract the connection process and the process of releasing objects to a tool class. The code in the tool class is as follows:

 

 

Package com. mucfc; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException; import java. SQL. statement; public class SqlDB {// define the database driver private static final String DBDRIVER = "com. mysql. jdbc. driver "; // database connection address private static final String DBURL =" jdbc: mysql: // localhost: 3306/school "; // school indicates database // database username private static final String DBUSER = "root"; // the password of the database on the computer private static final Stri Ng DBPASS = "christmas258 @"; public static Connection getconnection () {Connection conn = null; try {// 1. register the driver Class. forName (DBDRIVER); // 2. obtain the connection conn = DriverManager. getConnection (DBURL, DBUSER, DBPASS); // 3. create Statement object Statement stmt = conn. createStatement ();} catch (Exception e) {e. printStackTrace ();} return conn;} // 7. close public static void close (Statement statement, Connection connection) {Try {if (statement! = Null) statement. close (); if (connection! = Null) {connection. close () ;}} catch (SQLException e) {e. printStackTrace ();}}}}

Then we need to do the same thing. Add the following method in JdbcTest:

 

 

Public void CreateTableTest () {// obtain the Connection cnn2 = SqlDB. getconnection (); Statement statement = null; try {statement = cnn2.createStatement ();} catch (SQLException e) {e. printStackTrace ();} // prepare the SQL statement String SQL = "CREATE TABLE student (sid INT PRIMARY KEY, sname VARCHAR (20), age INT)"; System. out. println ("create table student ...... "); // call executeQuery to execute the query statement try again statement.exe cuteUpdate (SQL);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} SqlDB. close (statement, cnn2);} public void InsertTest () {// obtain the Connection cnn2 = SqlDB. getconnection (); Statement statement = null; try {statement = cnn2.createStatement ();} catch (SQLException e) {e. printStackTrace ();} // execute the insert operation System. out. println ("Inserting records into the table... "); String SQL =" insert into student "+" VALUES (100, 'xiaowen ', 18) "; try restarting statement.exe cuteUpdate (SQL);} catch (SQLException e1) {e1.printStackTrace ();} SQL = "insert into student" + "VALUES (101, 'salesman', 25)"; try restarting statement.exe cuteUpdate (SQL);} catch (SQLException e1) {e1.printStackTrace ();} SQL = "insert into student" + "VALUES (102, 'abel', 30)"; try restarting statement.exe cuteUpdate (SQL);} catch (SQLException e1) {e1.printStackTrace ();} SQL = "insert into student" + "VALUES (103, '小', 28)"; try restarting statement.exe cuteUpdate (SQL);} catch (SQLException e1) {e1.printStackTrace ();} System. out. println ("Inserted records into the table... "); SqlDB. close (statement, cnn2);} public void SelectTest () {// obtain the Connection cnn2 = SqlDB. getconnection (); Statement statement = null; try {statement = cnn2.createStatement ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // prepare the SQL statement String SQL = "select * from student"; // call executeQuery to execute the query statement ResultSet res = null; try {res = statement.exe cuteQuery (SQL);} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // After the query ends, res will point to the header. To obtain data, it must continuously point to the next row of the query result. If there is no data for the next row, 0 is returned. system. out. println ("select records from the table... "); try {while (res. next () {// first obtain the data int sid = res. getInt ("sid"); String sname = res. getString ("sname"); int age = res. getInt ("age"); // print the result System. out. print ("sid:" + sid); System. out. print ("sname:" + sname); System. out. println ("age:" + age) ;}} catch (SQLException e) {e. printStackTrace ();} try {res. close ();} catch (SQLException e) {e. printStackTrace ();} SqlDB. close (statement, cnn2 );}

Then, call:

 

 

JdbcTest jdbcTest=new JdbcTest();jdbcTest.CreateTableTest();jdbcTest.InsertTest();jdbcTest.SelectTest();

Result:

 

 

The amount of code is increasing .... It seems that the optimization is not good.

You can find out whether you can add the created database, inserted data, and searched data to SqlDB. Then, you only need to input the corresponding parameters when necessary. Next, we will modify SqlDB:

Store database creation, insertion, and search in a file.

 

Package com. mucfc; import java. SQL. *; /*** a simple encapsulation of the database operation class * @ author Lin bingwen * @ time 2015.4.30 */public class SqlDB {// define the database driver private static final String DBDRIVER = "com. mysql. jdbc. driver "; // database connection address private static final String DBURL =" jdbc: mysql: // localhost: 3306/school "; // school indicates the database username private static final String DBUSER = "root"; // The password private static final String DBPASS = "christmas258 @" on the computer @";/ * ** Create an initial operation for database Connection ** @ return database Connection handle */public static Connection getconnection () {Connection conn = null; try {// 1. register the driver Class. forName (DBDRIVER); // 2. obtain the connection conn = DriverManager. getConnection (DBURL, DBUSER, DBPASS); // 3. create Statement object Statement stmt = conn. createStatement ();} catch (Exception e) {e. printStackTrace ();} return conn;}/*** close resource * @ param statement is the instance that executes the command, and connection is the connection object */public static void Close (Statement statement, Connection connection) {try {if (statement! = Null) statement. close (); if (connection! = Null) {connection. close () ;}} catch (SQLException e) {e. printStackTrace () ;}}/ *** creates a database table. Note that the table exists in advance */public void CreatTable () {// 1. obtain the Connection connection = SqlDB. getconnection (); // 2. prepare the SQL statement String SQL = "CREATE TABLE student (sid INT PRIMARY KEY, sname VARCHAR (20), age INT)"; PreparedStatement preparedStatement = null; // 3. obtain the object try {preparedStatement = connection. prepareStatement (SQL);} catch (SQLException e) {e. printStackTrace ();} // 4. run SQLint num = 0 to the database. try {num = preparedStatement.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace ();} System. out. println ("with" + num + "affected records"); SqlDB. close (preparedStatement, connection);}/*** execute the insert operation * @ param parameter */public void InsertData (int id, String name, int age) {// 1. obtain the Connection connection = SqlDB. getconnection (); // 2. prepare SQL statement Str Ing SQL = "INSERT INTO student VALUES (?,?,?) "; PreparedStatement preparedStatement = null; // 3. obtain the object try {preparedStatement = connection. prepareStatement (SQL);} catch (SQLException e) {e. printStackTrace ();} // 4. to set an SQL parameter, you must specify the number of parameters and specify the type. The first statement below indicates that the first parameter of an SQL statement is of the int type, the parameter value is set to id, and so on try {preparedStatement. setInt (1, id); preparedStatement. setString (2, name); preparedStatement. setInt (3, age);} catch (SQLException e1) {e1.printStackTrace ();} // 5. submit it to the database for SQLin execution T num = 0; try {num = preparedStatement.exe cuteUpdate ();} catch (SQLException e) {e. printStackTrace ();} System. out. println ("with" + num + "affected records"); SqlDB. close (preparedStatement, connection);}/*** perform the search operation * @ param id student id */public void SelectDataWithId (int id) {// 1. obtain the Connection connection = SqlDB. getconnection (); // 2. prepare the SQL statement String SQL = "SELECT * FROM student where sid =? "; PreparedStatement preparedStatement = null; ResultSet res = null; // 3. obtain the object try {preparedStatement = connection. prepareStatement (SQL);} catch (SQLException e) {e. printStackTrace ();}/** 4. to set an SQL parameter, you must specify the number of parameters and specify the type. The first statement below indicates that the first parameter of an SQL statement is of the int type, and the parameter value is set to id, and so on */try {preparedStatement. setInt (1, id);} catch (SQLException e1) {e1.printStackTrace ();} // 5. run SQLtry {res = preparedStatement.exe cuteQuery (); while (res. next () {// first obtain the data int sid = res. getInt ("sid"); String sname = res. getString ("sname"); int age = res. getInt ("age"); // print the result System. out. print ("sid:" + sid); System. out. print ("sname:" + sname); System. out. println ("age:" + age) ;}} catch (SQLException e1) {e1.printStackTrace ();} SqlDB. close (preparedStatement, connection );}}

Then, you can directly use:

 

 

SqlDB sqlDB = new SqlDB (); sqlDB. creatTable (); sqlDB. insertData (309, "Xiaohong", 12); sqlDB. insertData (33, "small gray", 34); sqlDB. insertData (23, "", 145); sqlDB. selectDataWithId (33 );

 

Here, we use PreparedStatement for database operations. The following describes their differences.

Differences between Statement and PreparedStatement:

1. different syntaxes

Statement only supports static compilation, and SQL statements are completely written.

PreparedStatement supports precompilation? Number.

2. Different Efficiency

Statement sends an SQL Statement each time, which does not support caching and has low execution efficiency.

PreparedStatement supports pre-compilation and is cached in the database. You only need to send parameters to ensure fast execution.

3. Different security

Statement is easy to inject.

Injection: You can write special SQL statements to intrude into the database.

For example, to query the information of a user

General situation: SELECT * FROM user_list where username = xxx and password = xxx; (here xxx Should Be your username and password)

Injection: SELECT * FROM user_list where username = 'abc' or 1 = 1 -- password = xxx;

In this way, 1 = 1 Heng, and the "--" number is added before the password. The subsequent content becomes the comment and will not be executed. That is to say, you can query all user information without a password.

PreparedStatement, which specifies parameters in SQL statements, can prevent injection.

Conclusion: PreparedStatement is recommended because it is faster and safer.

VII. JDBC usage Summary

 

DriverManger: Driver manager class

To operate the database, you must first create a connection with the database to obtain the connection object.

Public static Connection getConnection (String url, String username, String password ){}

Connection: Connection interface:

Use the getConnection method of the DriverManger class to obtain the connection object. to execute an SQL Statement, you must use the Statement object (Statement)

Statement createStatement ();

Statement: Statement Interface

After obtaining the Statement object through the createStatement method of the connected object, the Statement object can execute SQL statements. The Statement provides executeUpdate and executeQuery statements to execute different SQL statements respectively.

Int executeUpdate (String SQL): The statement that executes the add, delete, modify, and delete operations. The returned value is the total number of rows in the operation.

ResultSet executeQuery (String SQL): executes a query statement. The returned value is a set of query results.

ResultSet: result set Interface

Boolean next (): This method can move the result set cursor downward. If there are still records, true is returned. If the traversal has ended, false is returned.

GetXXX (String columnName): this series of methods are used to return a large number of getXXX (String columnName) Methods to the field value result set Interface Based on the field name, such as getString and getInt.

GetXXX (int index): this series of methods is used to return the value of a field based on the index value of the field in the result set. There are a large number of getXXX methods in the result set, similar to the above, used to obtain the value of this field based on the index value. XXX is the data type of this field.

JDBC Statement object:
Statement: Above stmt = conn. createStatement ();
PrepareStatement: a pre-compiled Statement object. It is the sub-interface stmt = conn. prepareStatement (String SQL) of Statement, which is different from Statement.