This article describes the basic knowledge and skills of JDBC with examples. Share it with everyone for your reference. The specific analysis is as follows:
1. What is JDBC?
In layman's terms, JDBC technology uses a Java program to send SQL statements to the database. The database executes the SQL statements after receiving them, and returns the results to the Java program for management.
2. What are the conditions for using JDBC?
A) The address of the target database host
B) The port number occupied by the database software on the host
C) User name used to log in to the database
D) Password for that username
E) Connect to database
3.Principles of JDBC technology
We know that there are various types of databases, and the database standards and specifications produced by different manufacturers are different. At this time, if we use JAVA code to send SQL statements, we have to write one set after another according to different databases. A set of operation codes, which is a huge development cost for program developers, so SU When Company N developed JDBC technology, it stipulated a set of standard interfaces. Database manufacturers must provide a driver to implement this set of interfaces. Then, as long as program developers use the driver of the database during development, they must use a consistent method. To develop, there is no need to write a set of codes to adapt to different databases.
4. Core API in JDBC
|- Driver: The interface implemented by the driver class.
|-Connection connect(String url, Properties info) --used to connect to the database and get the connection object
Parameters that need to be set in Properties:
url: URL string of database connection. Protocol+database subprotocol+host+port+database
user: database user name
password: user’s password
|-Connection: interface to connect to the database
|- Statement createStatement() --Create a Statement object for sending sql statements
|- PreparedStatement prepareStatement(String sql) - Creates a PreparedStatement object for sending precompiled sql statements
|-CallableStatement prepareCall(String sql) --Create a CallableStatement object for calling stored procedures.
|-Statement: used to execute static sql statements
|-int executeUpdate(String sql) --Execute update operation (DDL+DML)
|-ResultSet executeQuery(String sql) --Execute query operation (DQL)
|- PreparedStatement: used to execute precompiled sql statements
|- int executeUpdate() -- perform update operation
|- ResultSet executeQuery() -- Execute query operation
|- CallableStatement: sql used to execute stored procedures
|- ResultSet executeQuery() --Call the stored procedure
|- ResultSet: Result set. Used to encapsulate the query data of the database
|- boolean next() --Move the record cursor to the next line
|- Object getObject(int columnIndex) -- Get the value of the field
After understanding which APIs, let’s use JDBC to send SQL statements~
5. Use the Statement object to operate the database
DDL and DML operations
Step 1
Import the package, because I am using a MySQL database, so to use JDBC technology, you must use the database driver provided by the MySQL database manufacturer. Therefore, the first step is to import the database driver package into the project.
Package name used: mysql-connector-java-5.1.7-bin.jar
Step 2
Create an ordinary class, add a method in it, and copy the code in the method as follows: //URL
private String url = "jdbc:mysql://localhost:3306/vmaxtam";
//user
private String user = "root";
//password
private String password = "root";
public void testDDL()throws Exception{
//1.Register driver
Class.forName("com.mysql.jdbc.Driver");
//2. Get the connection
Connection conn = DriverManager.getConnection(url, user, password);
//3. Create Statement object
Statement stmt = conn.createStatement();
//4. Prepare sql statement
String sql = "CREATE TABLE student(sid INT PRIMARY KEY,sname VARCHAR(20),age INT)";
//5. Send the sql statement through the statement object and return the execution result
int count = stmt.executeUpdate(sql);
//6.Print execution results
System.out.println("Affected "+count+" records");
}
//7. Close resources
if(statement!=null)
{
statement.close();
}
if(conn!=null)
{
conn.close();
}
If you want to perform DQL and DDL operations, you can write the SQL statement and then call the statement's executlUpdate method to execute the SQL statement for the database. This method returns an integer value indicating how many rows in the database are affected.
If we do not change the above program and want to issue SQL statements to the database again, then we have to write a program to connect again, and after the operation, we have to close the statement object and connection object, which is very cumbersome. Therefore, we generally extract the connection process and object release process into a tool class. The code in the tool class is as follows:
Copy the code as follows: public class sqlUtil {
private static String url = "jdbc:mysql://localhost:3306/vmaxtam";
private static String user = "root";
private static String password = "root";
// Get connection
public static Connection getconnection() {
Connection conn = null;
try {
// 1. Register driver
Class.forName("com.mysql.jdbc.Driver");
// 2. Get the connection
conn = DriverManager.getConnection(url, user, password);
// 3. Get the statement object
Statement statement = conn.createStatement();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
// 7. Close the resource
public static void close(Statement statement, Connection connection) {
{
try {
if (statement != null)
statement.close();
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Things to consider are:
1) A user only needs to register the driver once. There is no need to register the driver every time he connects to the database, so we write the process of registering the driver in a static code block.
2) The URL, user name, password and driver class name are hard-coded in the program. In order to be able to change the database or user without changing the code, we usually write this information into a configuration file.
The configuration file is written in the src directory of the project and is named db.properties
Copy the code as follows: url=jdbc:mysql://localhost:3306/vmaxtam
user=root
password=root
driverClass=com.mysql.jdbc.Drive
Then read the configuration file in sqlUtil, and finally optimize it into the following code. Copy the code as follows: public class sqlUtil {
private static String url = null;
private static String user = null;
private static String password = null;
private static String driverClass= null;
static{
try {
//1. Obtain the bytecode object
Classclazz = sqlUtil.class;
//2. Call getResourceAsStream to get the path
InputStream inputStream = clazz.getResourceAsStream("/db.properties");
Properties pro = new Properties();
pro.load(inputStream);
//3. Read parameters
url=pro.getProperty("url");
password=pro.getProperty("password");
user=pro.getProperty("user");
driverClass=pro.getProperty("driverClass");
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
System.out.println("Registration failed!" + e.getMessage());
throw new RuntimeException(e);
}
}
// Get connection
public static Connection getconnection() {
Connection conn = null;
try {
// Get connection
conn = DriverManager.getConnection(url, user, password);
// Get statement object
Statement statement = conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
// Close the resource
public static void close(Statement statement, Connection connection) {
{
try {
if (statement != null)
statement.close();
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
DQL operation
So how to use JDBC to query data in the database?
Copy the code code as follows: @Test
public void testdsl() throws Exception {
//Get connection
cnn2=sqlUtil.getconnection();
Statement statement = cnn2.createStatement();
//Prepare SQL statement
String sql = "select * from subject";
//Call executeQuery to execute the query statement
ResultSet res = statement.executeQuery(sql);
//After the query is completed, res will point to the table header. If you want to obtain data, you must continue to point to the next row of the query result. When there is no next row of data, 0 is returned.
while(res.next())
{
//Get the value of the field "sjid" in the query result, and make sure the type is clear
int id = res.getInt("sjid");
//Get the value of the field "sjname" in the query result, and the type must be clear
String name = res.getString("sjname");
System.out.println("ID:" + id + "NAME:" + name);
}
sqlUtil.close(statement, cnn2);
}
The above is the use of Statement objects to operate the database~
6. Use PreparedStatement to operate the database
The PreparedStatement object is actually a special Statement object that can precompile SQL statements. When you set the parameters, you can then execute the SQL statement~
DDL and DML operation copy code is as follows: package com.vmaxtam.sqltest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
public class PreparedStatementTest {
Connection connection = null;
@Test
public void ddldmlTest() throws Exception {
// 1. Get the connection
connection = sqlUtil.getconnection();
// 2. Prepare SQL statements, precompiled statements, and parameters? Number placeholder
String sql = "INSERT INTO SUBJECT VALUES(?,?)";
// 3. Get the object
PreparedStatement preparedStatement = connection.prepareStatement(sql);
/*
* 4. To set the SQL parameters, you need to know the parameter number and know its type. The first sentence below indicates: the first parameter of the SQL statement is of type int, and the parameter value is set to 3, and so on.
*/
preparedStatement.setInt(1, 3);
preparedStatement.setString(2, "English");
// 5. Hand it to the database to execute SQL
int num = preparedStatement.executeUpdate();
System.out.println("There are" + num + "records affected");
sqlUtil.close(preparedStatement, connection);
}
}
The above is to use the PreparedStatement object to send insertion statements. In the same way, DDL and DML statements can be sent in this way.
Benefits of PreparedStatement precompilation:
The precompilation of PreparedStatement allows you to query different targets by setting different parameters. On the database side, only a precompiled statement will be saved, but if you use Statement to send a statement, each time one is sent, one will be stored in the database. , which may take up a lot of memory.
DQL operation copy code code is as follows: @Test
public void dqlTest() throws Exception {
// 1. Get the connection
connection = sqlUtil.getconnection();
// 2. Prepare SQL statements, precompiled statements, and parameters? Number placeholder
String sql = "select * from subject where sjid=? or sjname=?";
// 3. Get the object
PreparedStatement preparedStatement = connection.prepareStatement(sql);
/*
* 4. To set the SQL parameters, you need to know the parameter number and know its type. The first sentence below indicates: the first parameter of the SQL statement is of type int, and the parameter value is set to 3, and so on.
*/
preparedStatement.setInt(1, 2);
preparedStatement.setString(2, "Chinese");
// 5. Hand it to the database to execute SQL
ResultSet rst = preparedStatement.executeQuery();
//6.Iterate the result set
while(rst.next())
{
int id = rst.getInt("sjid");
String name = rst.getString("sjname");
System.out.println("ID:" + id + "NAME:" + name);
}
//7. Close the connection
sqlUtil.close(preparedStatement, connection);
}
You can also call the executeQuery(); method, and iterate the output after getting the result set~
Since Statement and PreparedStatement are so similar, let’s compare their advantages and disadvantages~
The difference between Statement and PreparedStatement:
1. Different grammar
Statement only supports static compilation, and SQL statements are hard-coded.
PreparedStatement supports pre-compilation, use? Come and take a seat.
2. Different efficiency
Statement sends a SQL statement every time, does not support caching, and has low execution efficiency.
PreparedStatement supports pre-compilation and is cached in the database. It only needs to send parameters, and the execution efficiency is fast.
3. Different security
Statement is easily injected.
Injection: Crafty elements can write special SQL statements to compromise the database.
For example: To query the information of a certain user
General situation: SELECT * FROM user_list where username=xxx and password=xxx; (xxx here should fill in the user's own username and password)
Injection situation: SELECT * FROM user_list where username='abc' or 1=1 -- password=xxx;
In this way, 1=1 is equal, and the "--" sign is added before the password, and the following content becomes a comment and will not be executed. In other words, all user information can be queried without passwords.
PreparedStatement, because it specifies the parameters in the SQL statement, can prevent injection.
Conclusion: It is recommended to use PreparedStatement as it is faster and safer .
7. Use CallableStatement to execute stored procedures
Using CallableStatement only executes stored procedures. To create stored procedures, we still have to create them in the database.
Step 1
Now the database has created a stored procedure:
Copy the code code as follows: DELIMITER $
CREATE PROCEDURE pro_add(IN a INT , IN b VARCHAR(20),OUT c INT)
BEGIN
SELECT * FROM SUBJECT WHERE sjid=a OR sjname=b;
SET c=a+a+a+a;
END $
Step 2
Use java code to execute and get the output parameters. Copy the code as follows: @Test
public void calaST() throws Exception {
//Get connection
connection= sqlUtil.getconnection();
//Prepare SQL statement
String sql = "CALL pro_add(?,?,?)";
//Get the callableStatement object
CallableStatement cbs = connection.prepareCall(sql);
//Set input parameters, the same as preparedStatement
cbs.setInt(1, 3);
cbs.setString(2, "Math");
/*So how to set the output parameters?
* Need to register output parameters!
*/
cbs.registerOutParameter(3, java.sql.Types.INTEGER);//You need to use built-in objects to set parameter types
//Execute SQL statement
cbs.executeQuery();
//Use the getXXX method to get the output parameters of the corresponding position
Integer num= cbs.getInt(3);
System.out.println("a*4 is " + num);
//Close the resource
sqlUtil.close(cbs, connection);
}
I hope this article will be helpful to everyone’s Java programming.