Detailed JDBC usage

Introduction to JDBC

Java Database Connectivity, (Java Database Connectivity, referred to as JDBC) is an application programming interface used in the Java language to regulate how client programs access the database, and provides methods such as querying and updating data in the database. JDBC is also a trademark of Sun Microsystems. We usually say that JDBC is oriented to relational databases

The first JDBC program

Environment introduction

  • Create a database and a users table in the database
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
	id INT NOT NULL PRIMARY KEY,
	`name` VARCHAR (40),
	`password` VARCHAR (40),
	`email` VARCHAR(60),
	birthday DATE
);

INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(1,'Zhang San','123456','lew@qq.com','2001-04-12'),
(2,'Li Si','123456','cy@qq.com','2001-04-12'),
(3,'pock','123456','yxc@qq.com','2001-04-12');
  • Create a new java project jdbc, and import the database driver package mysql-connector-java-5.1.47 I use this version 5.1.47 here

Detailed code

1. Load the database driver

Class.forName("com.mysql.jdbc.Driver");//Fixed wording

Note: If you are connected to SQL server

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

2. Establish a connection

1. Database URL

 URL Used to identify the location of the database, the programmer passes URL address tell JDBC Which database should the program connect to, URL is written as:

jdbc:mysql:[]//localhost:3306/jdbcStudy? Parameter name: parameter value

Commonly used database URL address writing method:

Oracle: jdbc:oracle:thin:@localhost:1521:jdbcStudy 

SqlServer: jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=jdbcStudy 

MySql: jdbc:mysql://localhost:3306/jdbcStudy 

2. Connection object

Connection in the Jdbc program is used to represent the link to the database. Collection is the most important object in database programming. All interactions between the client and the database are completed through the connection object. The creation method is:

Connection conn = DriverManager.getConnection(url,username,password); 

wording
//2. User information and url
        String url="jdbc:mysql://localhost:3306/jdbcstudyuseUnicode=true&characterEncoding=utf8&useSSL=true";
        String username="root";//Need to change to your own username
        String password="123456";//Password to connect to the database
        //3. The connection is successful, and the database object Connection represents the database
        Connection connection = DriverManager.getConnection(url, username, password);

3. Execute the SQL statement

1. Statement object

Common methods of Statemen t:

statement.executeQuery();//Query operations return a ResultSet
statement.execute();//execute any sql
statement.executeUpdate();//Update, insert, delete, all use this, return a number of affected rows
//4. The object that executes sql Statement The object that executes sql
Statement statement = connection.createStatement();

2. PreperedStatement object

The essence is to treat the incoming parameters directly as characters, so that there are all characters directly, and logical operators are invalid

  • PreperedStatement can avoid SQL injection problems
  • Statement will cause the database to compile SQL frequently, which may cause database buffer overflow. PreparedStatement can precompile SQL to improve the execution efficiency of the database.
  • And PreperedStatement allows the use of placeholders to replace parameters in sql (to achieve precompilation), which simplifies the writing of sql statements.
//difference use? placeholders to replace parameters
String sql="insert into users(id,`name`,`password`,email,birthday) values(?,?,?,?,?)";
//Precompile, write sql first, and then do not execute
pst=conn.prepareStatement(sql);

4. Get the result

1. ResultSet object

The result set of the ResultSet query: encapsulates all query results


Get the specified data type
resultSet.getObject();//When the column type is not known use
//Use the specified type if the column type is known
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
...

ResultSet resultSet = statement.executeQuery(sql);//The returned result set is the result returned by my execution of sql
        //The returned result set, which encapsulates all the results of our query
        while(resultSet.next()){
            System.out.println("id:"+resultSet.getObject("id"));
            System.out.println("name:"+resultSet.getObject("name"));
            System.out.println("password:"+resultSet.getObject("password"));
            System.out.println("email:"+resultSet.getObject("email"));
            System.out.println("birthday:"+resultSet.getObject("birthday"));
        }

5. Release resources

After the Jdbc program is running, remember to release the objects that interact with the database created during the running of the program. These objects are usually ResultSet, Statement and Connection objects.

Note: To ensure that the resource release code can run, the resource release code must also be placed in the finally statement.

       if (rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

full code

1. Connect to the database

Create a file to store database information

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

2. Extract tool class JdbcUtils
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    //database driver
    private static String driver=null;
    //database name
    private static String username=null;
    //database address
    private static String url=null;
    //database password
    private static String password=null;
    static{
        try{
            //InputStream getResourceAsStream(String name): Finds a resource with the given name.
            InputStream ra = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            //Load the obtained stream into properties
            Properties prop = new Properties();
            prop.load(ra);
            //Get database connection information from the properties collection
            driver= prop.getProperty("driver");
            username= prop.getProperty("username");
            url= prop.getProperty("url");
            password= prop.getProperty("password");
            //Load the database driver
            Class.forName(driver);

        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    //Define a method to connect to the database
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //Define a method to release database resources
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs!=null){//If the result set returned by rs is not empty, it means that both conn and st are not empty
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }else{//If rs is empty, it means that no rs result set is returned, just close the conn connection and st execute the sql object
            try {
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

add operation

import com.liang.lesson02.utils.JdbcUtils;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            //Get the connection and call the method of connecting to the database in JdbcUtils
            conn = JdbcUtils.getConnection();
            //Get the execution object
            st=conn.createStatement();
            //write sql
            String sql="INSERT INTO users (id,`name`,`password`,email,birthday)" +
                    " VALUES(4,'Zhang San','aaaa','zs@qq.com','2002-04-10')";
            //execute sql
            try {
                int i = st.executeUpdate(sql);
                if (i > 0) {
                    System.out.println("Inserted successfully!");
                }
                //Here is the exception for catching duplicate inserts
            }catch (MySQLIntegrityConstraintViolationException e){
                    e.printStackTrace();
                    System.out.println("You have repeatedly inserted this data, please delete it first!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //Call the method of releasing resources in the JdbcUtils tool class
            JdbcUtils.release(conn,st,rs);
        }
    }
}

change the data

import com.liang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestUpdate {
    public static void main(String[] args) {
        //define global variables
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            //Connect to the database and get the statement object
            conn= JdbcUtils.getConnection();
            st=conn.createStatement();

            String sql="UPDATE users SET name='pock' WHERE id=4;";
            //Execute sql and return a number of affected rows
            int i = st.executeUpdate(sql);
            System.out.println(i);
            if (i>0){
                System.out.println("Successfully modified!");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(conn,st,rs);
        }
    }
}

delete data

import com.liang.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestDelete {
    public static void main(String[] args) {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            //Connect to the database and get the statement object
            conn= JdbcUtils.getConnection();
            st=conn.createStatement();

            String sql=" DELETE FROM users WHERE id=5;";
            //Execute sql and return the number of affected data
            int i = st.executeUpdate(sql);
            //System.out.println(i);
            if (i>0){
                System.out.println("successfully deleted!");
            }else{
                System.out.println("The data you deleted does not exist!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //release resources
            JdbcUtils.release(conn,st,rs);
        }
    }
}

Query data

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;

        try {
            //Connect to the database and get the statement object
            conn= JdbcUtils.getConnection();
            st=conn.createStatement();

            String sql="SELECT * FROM users";
            //Execute sql, and return the query result to return the resultSet result set object
            rs = st.executeQuery(sql);

            //Traversing the result set, the output is in the console
            while(rs.next()){
                System.out.println("id:"+rs.getInt("id"));
                System.out.println("name:"+rs.getString("name"));
                System.out.println("password:"+rs.getString("password"));
                System.out.println("email:"+rs.getString("email"));
                System.out.println("birthday:"+rs.getDate("birthday"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //release resources
            JdbcUtils.release(conn,st,rs);
        }
    }
}

Finally, let me say that the writing is not good, everyone is welcome to come and correct me

Tags: Java Database MySQL

Posted by naomi385 on Sun, 27 Nov 2022 20:52:51 +0300