JDBC basics tutorial
- 2020-04-01 03:38:57
- OfStack
This article illustrates the basics and techniques of JDBC. Share with you for your reference. Specific analysis is as follows:
1. What is JDBC?
In plain English, JDBC technology is a Java program that sends SQL statements to a database. The database executes the SQL statements when they are received and returns the results to the Java program for management.
2. What are the requirements for using JDBC?
A) the address of the target database host
B) the port number occupied by the database software on the host
C) the user name used to log in the database
D) the password of the user name
E) connect to the database
3. Principles of JDBC technology
We know that the database is there are a variety of types, different manufacturers database model to imitate and specification is different, this time, if we use JAVA code to send the SQL statement, will be according to the different database to write a set of operation and a set of code, the development cost of developers is very large, so the SUN at the time of development of JDBC technology, provides a set of standard interfaces, database production manufacturers must provide a drive to implement the interface, so as long as developers in development is used when the database driver, will develop in a consistent manner, Instead of having to write your own set of code to accommodate different databases.
4. Core apis in JDBC
|- Driver: interface implemented by the Driver class.
|-Connection connect(String url, Properties info) -- used to connect to a database to get a connection object
Parameters to be set in Properties:
Url: the url string for the database connection. Protocol + database subprotocol + host + port + database
User: database user name
Password: the user's password
| - Connection: Interface to connect to the database
| - Statement createStatement () -- create a Statement object to send SQL statements
| - PreparedStatement prepareStatement (String SQL) - creates a PreparedStatement object to send a precompiled SQL statement
| - the CallableStatement prepareCall (String SQL) Create a CallableStatement object to call the stored procedure.
|-statement: executes a static SQL Statement
| - int executeUpdate (String SQL) -- perform update operation (DDL+DML)
| - ResultSet executeQuery (String SQL) -- perform query operations (DQL)
|- PreparedStatement: to execute a precompiled SQL statement
|-int executeUpdate() -- performs an update operation
| - ResultSet executeQuery () -- perform the query operation
|-callablestatement: SQL used to execute stored procedures
| - ResultSet executeQuery () -- calling the stored procedure
|- ResultSet: ResultSet. Used to encapsulate the post-query data for a database
|-boolean next() -- moves the record cursor to the next line
|- Object getObject(int columnIndex) - gets the value on the field
After understanding which apis, let's use JDBC to send SQL statements
5. Use the Statement object to manipulate the database
DDL and DML operations
Step 1
Guide package, because I use MySQL database, so to use JDBC technology, you must use the database driver provided by the MySQL database manufacturer, so 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 a normal class, add a method to it, and follow these steps in the method
//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. Registered driver
Class.forName("com.mysql.jdbc.Driver");
//2. Gets the connection
Connection conn = DriverManager.getConnection(url, user, password);
//3. Create a Statement object
Statement stmt = conn.createStatement();
//Prepare the 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, return the result of execution
int count = stmt.executeUpdate(sql);
//6. Print the execution result
System.out.println(" affected "+count+" records ");
}
//7. Close resource
if(statement!=null)
{
statement.close();
}
if(conn!=null)
{
conn.close();
}If you want to do both 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, which returns an integer value indicating how many rows in the database are affected.
If we do not change the above procedure, and want to issue the SQL statement to the database again, then we have to write a program to connect again, and then close the statement object and connection object after the operation, which is very tedious. Therefore, we generally extract the procedures of connecting and releasing objects into a utility class. The code in the tool class is as follows:
public class sqlUtil {
private static String url = "jdbc:mysql://localhost:3306/vmaxtam";
private static String user = "root";
private static String password = "root";
//Gets the connection
public static Connection getconnection() {
Connection conn = null;
try {
//1. Registered driver
Class.forName("com.mysql.jdbc.Driver");
//2. Gets the connection
conn = DriverManager.getConnection(url, user, password);
//3. Get statement object
Statement statement = conn.createStatement();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//7. Close 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();
}
}
}
}The other thing to consider is:
A) a user only needs to register a driver on the line, not every connection to the database are registered drivers, so we put the registration of the driver process written in the static code block.
Two) url and user name, password and driver class name, in the program is written dead, in order to be able to change the database or change the user without changing the code, we usually write these information to a configuration file.
The configuration file is written in the SRC directory of the project, named db.properties
url=jdbc:mysql://localhost:3306/vmaxtam
user=root
password=root
driverClass=com.mysql.jdbc.DriveThe configuration file is then read in sqlUtil and optimized into the following code
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. Get the bytecode object
Class clazz = sqlUtil.class;
//2. Call getResourceAsStream to get the path
InputStream inputStream = clazz.getResourceAsStream("/db.properties");
Properties pro = new Properties();
pro.load(inputStream);
//3. Read parameter
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);
}
}
//Gets the connection
public static Connection getconnection() {
Connection conn = null;
try {
//Gets the connection
conn = DriverManager.getConnection(url, user, password);
//Gets the statement object
Statement statement = conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//Close 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 do you query data in a database using JDBC?
@Test
public void testdsl() throws Exception {
//Gets the connection
cnn2=sqlUtil.getconnection();
Statement statement = cnn2.createStatement();
//Prepare the SQL statement
String sql = "select * from subject";
//Call executeQuery to execute the query statement
ResultSet res = statement.executeQuery(sql);
//After the end of the query, the res will point to the header of the table. To get the data, it must point to the next row of the query result repeatedly. When there is no data in the next row, it will return 0.
while(res.next())
{
//Gets the value of the field "sjid" in the query result, and the type
is specified
int id = res.getInt("sjid");
//Gets the value of the field "sjname" in the query result, and the type
is specified
String name = res.getString("sjname");
System.out.println("ID:" + id + " NAME:" + name);
}
sqlUtil.close(statement, cnn2);
}So that's using the Statement object to manipulate the database
6. Use PreparedStatement to manipulate the database
A PreparedStatement object is a special Statement object that precompiles SQL statements so that when you set the parameters, you can execute the SQL Statement
DDL and DML operations
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 statement, precompile statement, parameter with? Number occupied
String sql = "INSERT INTO SUBJECT VALUES(?,?)";
//3. Get object
PreparedStatement preparedStatement = connection.prepareStatement(sql);
/*
* 4. Set up the SQL parameter You need the parameter number and you know its type The first sentence below means: SQL The first argument of the statement is int Type with the parameter value set to 3 And so on
*/
preparedStatement.setInt(1, 3);
preparedStatement.setString(2, " English ");
//5. Give the database to execute SQL
int num = preparedStatement.executeUpdate();
System.out.println(" There are " + num + " A record is affected ");
sqlUtil.close(preparedStatement , connection );
}
}This is where the PreparedStatement object is used to send the insert statement. Similarly, DDL and DML class statements can be sent in this way.
The benefits of PreparedStatement precompilation:
Precompiled PreparedStatement allows you to query different targets by setting different parameters. On the database side, only one precompiled Statement is saved, but if you use Statement to send a Statement, each Statement is saved in the database, which can take up a lot of memory.
DQL operation
@Test
public void dqlTest() throws Exception {
//1. Get the connection
connection = sqlUtil.getconnection();
//2. Prepare SQL statement, precompile statement, parameter with? Number occupied
String sql = "select * from subject where sjid=? or sjname=?";
//3. Get object
PreparedStatement preparedStatement = connection.prepareStatement(sql);
/*
* 4. Set up the SQL parameter You need the parameter number and you know its type The first sentence below means: SQL The first argument of the statement is int Type with the parameter value set to 3 And so on
*/
preparedStatement.setInt(1, 2);
preparedStatement.setString(2, " Chinese language and literature ");
//5. Give the database to execute SQL
ResultSet rst = preparedStatement.executeQuery();
//6. Iteration result set
while(rst.next())
{
int id = rst.getInt("sjid");
String name = rst.getString("sjname");
System.out.println("ID:" + id + " NAME:" + name);
}
//Close the connection
sqlUtil.close(preparedStatement, connection);
}ExecuteQuery () is also called; Method can be obtained after the result set iteration output ~
Since a Statement is similar to a PreparedStatement, compare their strengths and weaknesses
The difference between Statement and PreparedStatement:
1. Different grammar
Statement only supports static compilation; SQL statements are dead.
PreparedStatement supports precompilation, using? Sign to take up space.
2. Different efficiency
A Statement sends one SQL Statement at a time. It does not support caching and is inefficient to execute.
PreparedStatement supports precompilation, is cached in a database, and only needs to send parameters for quick execution.
3. Different security
Statements are easy to inject.
Injection: tricky molecules can write special SQL statements to break into databases.
For example, to query information about a user
SELECT * FROM user_list where username= XXX and password= XXX; (XXX here should have filled in his/her username and password for the user)
SELECT * FROM user_list where username=' ABC 'or 1=1 -- password= XXX;
So 1=1 is the same, and the word "--" is in front of the word "password," and the rest becomes a comment and is not executed. That is, it is possible to query all user information without a password.
PreparedStatement, because it specifies the parameters in the SQL statement, prevents injection.
Conclusion: PreparedStatement is recommended because it is faster and safer .
7. Execute stored procedures using a CallableStatement
Using the CallableStatement simply executes the stored procedure, and we're still creating the stored procedure in the database.
Step 1
Now the database is set up with a stored procedure:
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
Execute using Java code and get output parameters
@Test
public void calaST() throws Exception {
//Gets the connection
connection= sqlUtil.getconnection();
//Prepare the SQL statement
String sql = "CALL pro_add(?,?,?)";
//You get the callableStatement object
CallableStatement cbs = connection.prepareCall(sql);
//Set the input parameters, as in a preparedStatement
cbs.setInt(1, 3);
cbs.setString(2, " mathematics ");
/* So how do you set the output parameters?
* Need to register output parameters!
*/
cbs.registerOutParameter(3, java.sql.Types.INTEGER);//You need to use the built-in object to set the parameter type
//Execute the SQL statement
cbs.executeQuery();
//The getXXX method is used to obtain the output parameter
of the corresponding position
Integer num= cbs.getInt(3);
System.out.println("a*4 is " + num);
//Close resource
sqlUtil.close(cbs, connection);
}I hope this article has been helpful to your Java programming.