JDBC is introduced

JDBCJava DataBase Connectivity is a bridge between Java and databases, a specification rather than an implementation that can execute SQL statements. It consists of a set of classes and interfaces written in the Java language. There are implementations for different types of databases,The code in this article is implemented against the MySQL database.

The JDBC framework

double

What it does: In this architecture, a Java Applet or application accesses a data source directly.

Condition: The Driver is required to interact with the accessed database.

Mechanism: User commands are passed to a database or other data source and the results are returned.

Deployment: The data source can be on another machine and the user is connected over a network, called a C/S configuration (which can be an Intranet or the Internet).

Three layers

JDBC Programming Steps

1. Load the JDBC driver of the corresponding database and initialize it

1.1 Importing Dedicated JAR Packages (Different databases Use different JAR packages)

• Access to the MySQL database requires the use of third-party classes, and these third-party classes are compressed into one. Jar file. The mysql-connector-java-5.0.8-bin.jar package can be downloaded online or found in the mysql installation directory. Typically, you download the JAR package and place it in the project’s lib directory, in this case E:\project\ J2SE \lib, and then import the jar package in Eclipse.

英 文 : Right-click project -> Build path -> Configure build path -> Add external Jar

Add External jars to your project-> Property -> Java Build Path -> Libaries

If you have not completed the package guide for the above steps, a ClassNotFoundException will be thrown

1.2 Initializing the Driver

try {
        Class.forName("com.mysql.jdbc.Driver");		
        } catch (ClassNotFoundException e) { 				
            e.printStackTrace();
        }
Copy the code

2. Establish a Connection between JDBC and the database

You need to provide: IP address of the database server :127.0.0.1 (This is the local computer. If you want to connect to the database on another computer, you need to fill in the corresponding IP address. You can also fill in localhost)

• • Database port number: 3306 (mysql port number) Database name exam (Set this parameter based on the name in your own database) Encoding mode UTF-8 Account root Password admin (If you do not use the default account and password when creating the database, enter your own account and password. Some databases have empty default passwords.)

Connection c = DriverManager.getConnection("JDBC: mysql: / / 127.0.0.1:3306 / exam? characterEncoding=UTF-8"."root"."admin");
Copy the code

A Connection is an interface that responds to a particular database Connection. It is used with a packet guide and must be closed at the end of the program. The getConnection method also needs to catch SQLException exceptions.

Because it is necessary to establish a connection with the database when adding, deleting, modifying and checking the database, so you can write the connection in the project as a tool method, which can be directly called when using:

        /** * get database connection *@returnA database connection */
public static Connection getConnection(a){
		Connection conn = null;
		 try {
			 	// Initialize the Driver class com.mysql.jdbc.driver
	            Class.forName("com.mysql.jdbc.Driver");
	            conn = DriverManager.getConnection("JDBC: mysql: / / 127.0.0.1:3306 / exam? characterEncoding=UTF-8"."root"."admin");
	            // This class is in mysql-connector-java-5.0.8-bin.jar. If you forget the first step, throw ClassNotFoundException
	        } catch (ClassNotFoundException e) { 				
	            e.printStackTrace();
	        }catch (SQLException e) {							
	            e.printStackTrace();
	        }
		 return conn;
	}
Copy the code

3. Create a Statement or PreparedStatement interface to execute SQL statements

3.1 Using the Statement Interface

After the Statement interface is created, you can execute SQL statements to add, delete, modify, and query a database. Among them, add, delete and change only need to change the content of SQL statements can be completed, but the query is slightly complicated. String concatenation is used in Statement, which has some disadvantages such as complex syntax and error prone, as described in the following comparison. Therefore, statements are rarely used in the actual process, so detailed codes are provided in PreparedStatement.

String concatenation SQL statements are very complicated, there are a lot of single quotation marks and double quotation marks mixed, easy to error.

Statement s = conn.createStatement();
// Prepare the SQL statement
// Note that strings are quoted in single quotes.
String sql = "insert into t_courses values(null,"+"' mathematics')";
// String concatenation in statement is problematic
s.execute(sql);
System.out.println("Insert statement executed successfully");
Copy the code

3.2 Using the PreparedStatement interface

        /** * Add course *@paramCourseName */
	public void addCourse(String courseName){
		String sql = "insert into t_course(course_name) values(?) ";  
 // This statement reserves a question mark ("? ") for each IN argument. ) as a placeholder
		Connection conn = null;				// Connect to the database
		PreparedStatement pstmt = null;		/ / create the statement
		try{
			conn = DbUtil.getConnection();
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, courseName); // Assign a placeholder value
			pstmt.executeUpdate();			/ / execution
		}catch(SQLException e){
			e.printStackTrace();
		}
		finally{
			DbUtil.close(pstmt);
			DbUtil.close(conn);		// Must be closed}}Copy the code

        /** * delete course *@param courseId
	 */
	public void delCourse(int courseId){
		String sql = "delete from t_course where course_id = ?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DbUtil.getConnection();
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setInt(1, courseId);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			DbUtil.close(pstmt);
			DbUtil.close(conn);		// Must be closed}}Copy the code

        /** * Modify course *@param courseId
	 * @param courseName
	 */
	public void modifyCourse(int courseId,String courseName){
		String sql = "update t_course set course_name =? where course_id=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DbUtil.getConnection();
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, courseName);  // Assign a placeholder value using the set method in Preparedstatement
			pstmt.setInt(2, courseId);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			DbUtil.close(pstmt);
			DbUtil.close(conn);		// Must be closed}}Copy the code

3.3 Summary of this section

As can be seen from the above add, delete and change procedures, their code is much the same, mainly SQL statements are different, other places are almost identical. There are a few things to note:

1. In PreparedStatement, SQL statements use placeholders instead of string concatenation. “?” This is where the placeholder is used. In addition to avoiding the hassle of concatenating strings for statements, this approach also improves performance. Every time the SQL statement is the same, the Java class is not compiled again, which can significantly improve performance.

String sql = "update t_course set course_name =? where course_id=?";
Copy the code

The placeholder is then assigned using the set method of PSTMT created by the PreparedStatement interface. Note that the parameter index here starts at 1.

pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, courseName);  // Assign a placeholder value using the set method in Preparedstatement
pstmt.setInt(2, courseId);
pstmt.executeUpdate();
Copy the code

Pstmt.executeupdate () is used for pstmt.executeUpdate(). The following query will be different, please note.

2. In the process of adding, if the amount of data to be added is large, you can add it in batches. The PreparedStatement interface provides methods for the corresponding batch operations.

for(int i=1; i<100; i++){ pstmt.setInt(1.8000+i);
     pstmt.setString(2."Zhao _"+i);
     pstmt.addBatch();
// Batch update
     if(i%10= =0){ pstmt.executeBatch(); }}Copy the code

Let’s look at a slightly more troublesome query operation:

        /** * Query course *@return* /
	public List<Course> findCourseList(a){
		String sql = "select * from t_course order by course_id";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		// Create a collection object to store the queried data
		List<Course> courseList = new ArrayList<>();
		try {
			conn = DbUtil.getConnection();
			pstmt = (PreparedStatement) conn.prepareStatement(sql);
			rs = (ResultSet) pstmt.executeQuery();
			while (rs.next()){
				int courseId = rs.getInt("course_id");
				String courseName = rs.getString("course_name");
				// Each record corresponds to an object
				Course course = new Course();
				course.setCourseId(courseId);
				course.setCourseName(courseName);
				// Put the object into the collectioncourseList.add(course); }}catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			DbUtil.close(pstmt);
			DbUtil.close(conn);		// Must be closed
		}
		return courseList;
	}
Copy the code

The query operation is updated using executeQuery(). Other related issues are explained in step 4 (Processing and displaying results).

4. Process and display the results

ResultSet rs = s.executeQuery(sql);
Copy the code

while (rs.next()){
		int courseId = rs.getInt("course_id");
		String courseName = rs.getString("course_name");
		// Each record corresponds to an object
		Course course = new Course();
// In my project I created the course class, which defines the set method, so I pass the queried value to course, or I can print it directly to the console
		course.setCourseId(courseId);
		course.setCourseName(courseName);
		// Put the object into the collection
		courseList.add(course);
		}
Copy the code

 course.setCourseId(courseId);
 course.setCourseName(courseName); 
Copy the code

5. Release resources

In the process of JDBC coding, we created a Connection, ResultSet and other resources, these resources must be closed after the use. Follow the principle of closing from the inside out. Since such close operations are used in the add, delete, modify and check operations, in order to keep the code simple and increase its reuse, I will write these close operations into a tool class together with the method to establish the connection.

/** * encapsulates three closing methods *@param pstmt
	 */
	public static void close(PreparedStatement pstmt){
		if(pstmt ! =null) {// Avoid null pointer exceptions
			try{
				pstmt.close();
			}catch(SQLException e){ e.printStackTrace(); }}}public static void close(Connection conn){
		if(conn ! =null) {try {
				conn.close();
			} catch (SQLException e) {
				// TODO: handle exceptione.printStackTrace(); }}}public static void close(ResultSet rs){
		if(rs ! =null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO: handle exceptione.printStackTrace(); }}}Copy the code

summary

The differences, advantages and disadvantages of Statement and PreparedStatement

Both are used to execute SQL statements

Different: A PreparedStatement is created based on an SQL Statement. It can set parameters and specify corresponding values instead of using string concatenation.

Advantages of preparedStatements:

Execute vs. executeUpdate

Similarities: Both can add, delete, and modify operations.

Difference:

1. Execute the query statement and fetch the result from getResult. ExecuteUpdate cannot execute the query.

Execute Returns Boolean. True indicates that a query statement is executed. False indicates that an INSERT, delete, or update statement is executed. The return value from executeUpdate is int, indicating how many pieces of data were affected.