高级应用--JDBC（一）

作者: RicherYY | 来源:发表于2020-08-14 00:06 被阅读0次

为什么要使用JDBC？
如何使用JDBC？
使用JDBC进行增删改查
使用预编译PreparedStatement进行改进
execute与executeUpdate的区别
DAO思想与设计

1.为什么要使用JDBC？

每个数据库厂家使用的数据库不同，所以Sun公司统一制定了数据库连接规范

2.如何使用JDBC？

使用JDBC的基本操作

导入数据库驱动包
需要到网络下载这里注意一下MySQL数据库版本号
初始化驱动

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

建立与数据库的连接

Connection connection = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC",
"root",
"123456");

创建Statement

Statement st = connection.createStatement();

创建并执行SQL语句

String addCodeSQL = "insert into test1 values(null,'006')";
st.execute(addCodeSQL);

关闭连接

st.close();
connection.close();

完整使用JDBC代码

package jdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
public class TestJDBC {
        public static void main(String[] args) {
        private static String DRIVER = "com.mysql.cj.jdbc.Driver";
        private static String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
        private static String USERNAME = "root";
        private static String PASSWORD = "123456";
 
        Connection c = null;
        Statement s = null;
        try {
            Class.forName(DRIVER);
 
            c = DriverManager.getConnection(URL,USERNAME,PASSWORD);
 
            s = c.createStatement();
 
            String sql = "insert into hero values(null," 006")";
 
            s.execute(sql);
 
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            // 数据库的连接时有限资源，相关操作结束后，养成关闭数据库的好习惯
            // 先关闭Statement
            if (s != null)
                try {
                    s.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            // 后关闭Connection
            if (c != null)
                try {
                    c.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
 
        }
 
    }
}

MySQL 8.0 以上版本驱动包版本 [mysql-connector-java-8.0.16.jar]

com.mysql.jdbc.Driver 更换为 com.mysql.cj.jdbc.Driver。

3.使用JDBC进行增删改查

3.1 增、删、改

增、删、改都是用同一个方式拼接字符串就可以了

package test;

import java.sql.*;

public class JavaTest {

    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static String USERNAME = "root";
    private static String PASSWORD = "123456";

    public static void main(String[] args) {

        try {
            // 1.创建驱动
            Class.forName(DRIVER);
            System.out.println("数据库驱动加载成功！");

            //2.连接数据库
            Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
            System.out.println("连接成功"+connection);
         
            //3.创建sql语句
            String addCodeSQL = "insert into test1 values(null,'006')";

            //4.执行sql语句
            Statement st = connection.createStatement();
            st.execute(addCodeSQL);

            System.out.println("执行成功！");

            //5.释放资源
            st.close();
            connection.close();

        } catch (SQLException | ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

3.2 查询

查询的结果是因为返回一个集合，所以要使用ResultSet结果集来接收
使用executeQuery()方法来执行查询语句

package test;

import java.sql.*;

public class JavaTest {

    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static String USERNAME = "root";
    private static String PASSWORD = "123456";

    public static void main(String[] args) {

        try {
            // 1.创建驱动
            Class.forName(DRIVER);
            System.out.println("数据库驱动加载成功！");

            //2.连接数据库
            Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);

            System.out.println("连接成功"+connection);

            //3.查询语句
            String searchSQL = "select * from test1";

            //4.执行sql语句
            Statement st = connection.createStatement();      

            //4.1查询结果集
            ResultSet rs = st.executeQuery(searchSQL);
            while (rs.next()){
                int id = rs.getInt(1);
                String code = rs.getString(2);
                System.out.print("ID: " + id);
                System.out.print(" Code: " + code + "\n");
            }
            rs.close();
            System.out.println("执行成功！");


            //5.释放资源
            st.close();
            connection.close();

        } catch (SQLException | ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

4.使用预编译PreparedStatement进行改进

4.1 为什么要使用PreparedStatement？

最基本的Statement方式没有办法防止SQL注入攻击 OR 1=1
手动拼接字符串太容易出错，所以使用占位符?来进行占位

4.2 怎样使用PreparedStatement？

以插入示例

        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String addSQL = "insert into test1 values(null,?)";
        PreparedStatement ps = connection.prepareStatement(addSQL);
        ps.setString(1,"008");
        ps.execute();
        ps.close();
        connection.close();
        System.out.println("执行成功！");

6.DAO设计思想

package test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JavaTest {

    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC";
    private static String USERNAME = "root";
    private static String PASSWORD = "123456";


    public static void main(String[] args) throws SQLException, ClassNotFoundException {



        //Student student1 = new Student("009");
        //add(student1);

        //Student student2 = new Student(9);
        //delete(student2);

        //Student student3 = new Student(8,"010");
        //update(student3);

        //list();

    }


    //public static void add(Hero h)

    public static void add(Student student) throws SQLException, ClassNotFoundException {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String addSQL = "insert into test1 values(null,?)";
        PreparedStatement ps = connection.prepareStatement(addSQL);
        ps.setString(1,student.getCode());
        ps.execute();
        ps.close();
        connection.close();
        System.out.println("执行成功！");
    }



    //public static void delete(Hero h)
    public static void delete(Student student) throws SQLException, ClassNotFoundException {

        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String deleteSQL = "delete from test1 where id = ?";
        PreparedStatement ps = connection.prepareStatement(deleteSQL);
        ps.setInt(1,student.getId());
        ps.execute();
        ps.close();
        connection.close();
        System.out.println("执行成功！");
    }


    //public static void update(Hero h)
    public static void update(Student student) throws SQLException, ClassNotFoundException {
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String updateSQL = "update test1 set code = ? where id = ?";
        PreparedStatement ps = connection.prepareStatement(updateSQL);
        ps.setString(1,student.getCode());
        ps.setInt(2,student.getId());
        ps.execute();
        ps.close();
        connection.close();
        System.out.println("执行成功！");
    }

    //public static List<Hero> list();
    public static List<Student> list() throws SQLException, ClassNotFoundException {
        List<Student> list= new ArrayList<>();
        Class.forName(DRIVER);
        Connection connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        String searchSQL = "select * from test1";
        PreparedStatement ps = connection.prepareStatement(searchSQL);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            int id = rs.getInt(1);
            String code = rs.getString(2);
            Student student = new Student(id,code);
            list.add(student);
        }
        list.forEach(a-> System.out.println(a));
        return list;
    }

}

高级应用--JDBC（一）

1.为什么要使用JDBC？

2.如何使用JDBC？

使用JDBC的基本操作

完整使用JDBC代码

3.使用JDBC进行增删改查

3.1 增、删、改

3.2 查询

4.使用预编译PreparedStatement进行改进

4.1 为什么要使用PreparedStatement？

4.2 怎样使用PreparedStatement？

6.DAO设计思想

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读