侧边栏壁纸
博主头像
coydone博主等级

记录学习,分享生活的个人站点

  • 累计撰写 306 篇文章
  • 累计创建 51 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

JDBC的CRUD

coydone
2022-01-09 / 0 评论 / 0 点赞 / 301 阅读 / 12,652 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-05-02,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

操作和访问数据库

数据库连接被用于向数据库服务器发送命令和 SQL 语句,并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。

在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式:

  • Statement:用于执行静态 SQL 语句并返回它所生成结果的对象。

  • PrepatedStatement:SQL语句被预编译并存储在此对象中,可以使用此对象多次高效地执行该语句。

  • CallableStatement:用于执行 SQL 存储过程。

使用Statement操作数据表的弊端

通过调用 Connection 对象的 createStatement() 方法创建该对象。该对象用于执行静态的 SQL 语句,并且返回执行结果。

Statement 接口中定义了下列方法用于执行 SQL 语句:

int excuteUpdate(String sql):执行更新操作INSERT、UPDATE、DELETE
ResultSet executeQuery(String sql):执行查询操作SELECT

但是使用Statement操作数据表存在弊端:

  • 问题一:存在拼串操作,繁琐

  • 问题二:存在SQL注入问题

SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的 SQL 语句段或命令(如:SELECT user, password FROM user_table WHERE user='a' OR 1 = ' AND password = ' OR '1' = '1'),从而利用系统的 SQL 引擎完成恶意行为的做法。

对于 Java 而言,要防范 SQL 注入,只要用 PreparedStatement(从Statement扩展而来)取代 Statement 就可以了。

代码演示:

public class StatementTest {
    // 使用Statement的弊端:需要拼写sql语句,并且存在SQL注入的问题
    @Test
    public void testLogin() {
        Scanner scan = new Scanner(System.in);
        System.out.print("用户名:");
        String userName = scan.nextLine();
        System.out.print("密   码:");
        String password = scan.nextLine();
        // SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
        String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
            + "'";
        User user = get(sql, User.class);
        if (user != null) {
            System.out.println("登陆成功!");
        } else {
            System.out.println("用户名或密码错误!");
        }
    }
    // 使用Statement实现对数据表的查询操作
    public <T> T get(String sql, Class<T> clazz) {
        T t = null;
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            // 1.加载配置文件
            InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
            Properties pros = new Properties();
            pros.load(is);

            // 2.读取配置信息
            String user = pros.getProperty("user");
            String password = pros.getProperty("password");
            String url = pros.getProperty("url");
            String driverClass = pros.getProperty("driverClass");

            // 3.加载驱动
            Class.forName(driverClass);

            // 4.获取连接
            conn = DriverManager.getConnection(url, user, password);
            st = conn.createStatement();
            rs = st.executeQuery(sql);

            // 获取结果集的元数据
            ResultSetMetaData rsmd = rs.getMetaData();
            // 获取结果集的列数
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) 
                t = clazz.newInstance();
            for (int i = 0; i < columnCount; i++) {
                // //1. 获取列的名称
                // String columnName = rsmd.getColumnName(i+1);
                // 1. 获取列的别名
                String columnName = rsmd.getColumnLabel(i + 1);
                // 2. 根据列名获取对应数据表中的数据
                Object columnVal = rs.getObject(columnName);
                // 3. 将数据表中得到的数据,封装进对象
                Field field = clazz.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(t, columnVal);
            }
            return t;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 关闭资源
        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();
            }
        }
    }
    return null;
}

所以需要使用PreparedStatement替换Statement。

PreparedStatement的使用

PreparedStatement介绍

可以通过调用 Connection 对象的 preparedStatement(String sql) 方法获取 PreparedStatement 对象

PreparedStatement 接口是 Statement 的子接口,它表示一条预编译过的 SQL 语句

PreparedStatement 对象所代表的 SQL 语句中的参数用问号(?)来表示,调用 PreparedStatement 对象的 setXxx() 方法来设置这些参数. setXxx() 方法有两个参数,第一个参数是要设置的 SQL 语句中的参数的索引(从 1 开始),第二个是设置的 SQL 语句中的参数的值。

PreparedStatement vs Statement

代码的可读性和可维护性。

PreparedStatement 能最大可能提高性能:

  • DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用,所以语句在被DBServer的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中就会得到执行。

  • 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。

  • (语法检查,语义检查,翻译成二进制命令,缓存)

  • PreparedStatement 可以防止 SQL 注入。

Java与SQL对应数据类型转换表

Java类型 SQL类型
boolean BIT
byte TINYINT
short SMALLINT
int INTEGER
long BIGINT
String CHAR,VARCHAR,LONGVARCHAR
byte array BINARY , VAR BINARY
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP

使用PreparedStatement实现CRUD操作

package com.coydone;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;
import java.util.Scanner;

//占位符的方式crud
public class Test03 {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    @Before
    public void beforeMethod(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名", "root", "root123");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    @After
    public void afterMethod(){
        try {
            if (rs != null) rs.close();
            if (ps != null) ps.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //添加数据
    @Test
    public void addEmp(){
        Scanner input = new Scanner(System.in);
        System.out.print("请输入要添加的员工姓名:");
        String ename = input.next();
        System.out.print("请输入要添加的员工工作:");
        String job = input.next();
        System.out.print("请输入要添加的上级编号:");
        String mgr = input.next();
        System.out.print("请输入要添加的入职日期:");
        String hiredate = input.next();
        System.out.print("请输入要添加的员工工资:");
        String sal = input.next();
        System.out.print("请输入要添加的员工奖金:");
        String comm = input.next();
        System.out.print("请输入要添加的员工部门编号:");
        String deptno = input.next();
        try {
            String sql = "INSERT INTO employee (ename,job,mgr,hiredate,sal,comm,deptno) VALUES (?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            //给占位符赋值
            ps.setString(1,ename);
            ps.setString(2,job);
            ps.setString(3,mgr);
            ps.setString(4,hiredate);
            ps.setString(5,sal);
            ps.setString(6,comm);
            ps.setString(7,deptno);
            int i = ps.executeUpdate();
            if (i>0) System.out.println("添加成功!");
            else System.out.println("添加失败!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //删除数据
    @Test
    public void deleteEmp(){
        Scanner input = new Scanner(System.in);
        System.out.print("请输入要删除的员工编号:");
        String empno = input.next();
        try {
            String sql = " delete from employee where empno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,empno);
            int i = ps.executeUpdate();
            if (i>0) System.out.println("删除成功!");
            else System.out.println("删除失败!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //修改数据
    @Test
    public void updateEmp(){
        Scanner input = new Scanner(System.in);
        System.out.print("请输入要修改的员工编号:");
        String empno = input.next();
        System.out.print("请输入修改后的姓名:");
        String ename = input.next();
        System.out.print("请输入修改后的奖金:");
        String comm = input.next();
        try {
            String sql = " update employee set ename= ? ,comm = ? where empno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,ename);
            ps.setString(2,comm);
            ps.setString(3,empno);
            int i = ps.executeUpdate();
            if (i>0) System.out.println("修改成功!");
            else System.out.println("修改失败!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //查询数据
    @Test
    public void queryEmp(){
        Scanner input = new Scanner(System.in);
        System.out.print("请输入要查询的员工编号:");
        String no = input.next();
        try {
            String sql = "select * from employee where empno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,no);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                Integer mgr = rs.getInt("mgr");
                java.sql.Date hiredate = rs.getDate("hiredate");
                double sal = rs.getDouble("sal");
                double comm = rs.getDouble("comm");
                int deptno = rs.getInt("deptno");
                System.out.println(empno + "\t"+ename+"\t"+job+"\t"+mgr+"\t"+hiredate+"\t"+sal+"\t"+comm+"\t"+deptno);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

ResultSet与ResultSetMetaData

ResultSet

查询需要调用PreparedStatement 的 executeQuery() 方法,查询结果是一个ResultSet 对象

ResultSet 对象以逻辑表格的形式封装了执行数据库操作的结果集,ResultSet 接口由数据库厂商提供实现

ResultSet 返回的实际上就是一张数据表。有一个指针指向数据表的第一条记录的前面。

ResultSet 对象维护了一个指向当前数据行的游标,初始的时候,游标在第一行之前,可以通过 ResultSet 对象的 next() 方法移动到下一行。调用 next()方法检测下一行是否有效。若有效,该方法返回 true,且指针下移。相当于Iterator对象的 hasNext() 和 next() 方法的结合体。

当指针指向一行时, 可以通过调用 getXxx(int index) getXxx(int columnName) 获取每一列的值。

  • 例如:getInt(1), getString(“name”)

  • 注意:Java与数据库交互涉及到的相关Java API中的索引都从1开始。

ResultSet 接口的常用方法:

  • boolean next()

  • getString()

ResultSetMetaData

可用于获取关于 ResultSet 对象中列的类型和属性信息的对象。

ResultSetMetaData meta = rs.getMetaData();

getColumnName(int column):获取指定列的名称
getColumnLabel(int column):获取指定列的别名
getColumnCount():返回当前 ResultSet 对象中的列数。 
getColumnTypeName(int column):检索指定列的数据库特定的类型名称。 
getColumnDisplaySize(int column):指示指定列的最大标准宽度,以字符为单位。 
isNullable(int column):指示指定列中的值是否可以为 null。 
isAutoIncrement(int column):指示是否自动为指定列进行编号,这样这些列仍然是只读的。

关于ResultSetMetaData

1、如何获取 ResultSetMetaData: 调用 ResultSet 的 getMetaData() 方法即可。

2、获取 ResultSet 中有多少列:调用 ResultSetMetaData 的 getColumnCount() 方法。

3、获取 ResultSet 每一列的列的别名是什么:调用 ResultSetMetaData 的getColumnLabel() 方法。

资源的释放

释放ResultSet,Statement,Connection。

数据库连接(Connection)是非常稀有的资源,用完后必须马上释放,如果Connection不能及时正确的关闭将导致系统宕机。Connection的使用原则是尽量晚创建,尽量早的释放。

可以在finally中关闭,保证及时其他代码出现异常,资源也一定能被关闭。

在BaseDao中封装数据库操作

DAO:Data Access Object访问数据信息的类和接口,包括了对数据的CRUD(Create、Retrival、Update、Delete),而不包含任何业务相关的信息。有时也称作:BaseDAO。

作用:为了实现功能的模块化,更有利于代码的维护和升级。

package com.coydone.dao;
import java.sql.*;
//封装jdbc
public class BaseDao {
    //连接数据库参数
    private static String driverClass = "com.mysql.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/kgc7_22";
    private String username = "root";
    private String password = "root123";
    //静态代码块获取驱动
    static {
        try {
            Class.forName(driverClass);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取数据库连接方法
    public Connection getconn(){
        try {
            return DriverManager.getConnection(url, username, password);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    //关闭数据库连接
    public void close(Connection conn, Statement stat, ResultSet rs){
        try {
            if (rs != null) rs.close();
            if (stat != null) stat.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //增删改操作封装
    public Integer executeUpdate(String sql, Object[] obj){
        //获取数据库连接对象
        Connection conn = this.getconn();
        try {
            //预编译sql
            PreparedStatement ps = conn.prepareStatement(sql);
            //占位符
            for (int i = 0; i < obj.length ; i++) {
                ps.setObject(i+1,obj[i]);
            }
            int i = ps.executeUpdate();
            //关闭连接
            this.close(conn,ps,null);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //查询封装,有返回的结果集,传入的参数为sql语句,和可变参数
    public ResultSet executeQuery(String sql,Object... obj) throws SQLException {
        Connection conn = this.getconn();
        PreparedStatement ps = conn.prepareStatement(sql);
        //占位符
        for (int i = 0; i < obj.length; i++) {
            ps.setObject(i+1,obj[i]);
        }
        ResultSet rs = ps.executeQuery();
        //关闭资源
        //this.close(conn,ps,rs);
        return rs;
    }
}

使用反射、配置文件形式封装jdbc操作

package com.coydone.dao;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

//使用反射 配置文件形式封装jdbc操作
public class Base2Dao<T> {
    //获取配置文件内容
    //封装数据
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    //读取配置文件
    static {
        try {
            //加载并读取文件
            //p.load(new FileReader("database.properties"));
            //获取配置文件内容
            InputStream is = Base2Dao.class.getClassLoader().getResourceAsStream("database.properties");
            //创建对象
            Properties p = new Properties();
            //把流中的数据加载到Properties对象中
            p.load(is);

            driverClass = p.getProperty("driverClass");
            url = p.getProperty("url");
            username = p.getProperty("username");
            password = p.getProperty("password");

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

    //加载驱动
    static {
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //封装获取数据库连接对象
    public Connection getConn() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
            //进行抛出异常处理
            throw new RuntimeException(e);
        }
    }

    //关闭连接,释放资源
    public void close(Connection conn, Statement stat, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (stat != null) stat.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //增删改操作
    public Integer executeUpdate(String sql, Object[] obj) throws Exception{
        //获取数据库连接对象
        Connection conn = this.getConn();
        PreparedStatement ps = conn.prepareStatement(sql);
        //占位符
        for (int i = 0; i < obj.length ; i++) {
            ps.setObject(i+1,obj[i]);
        }
        int i = ps.executeUpdate();
        //关闭连接
        this.close(conn,ps,null);
        return i;
    }

    //查询操作
    public List<T> executeQuery(String sql,Class c,Object... obj) throws Exception {
        //存放结果集
        List<T> list = new ArrayList<>();
        //获取连接
        Connection conn = this.getConn();
        PreparedStatement ps = conn.prepareStatement(sql);

        for (int i = 0; i < obj.length; i++) {
            ps.setObject(i+1,obj[i]);
        }

        ResultSet rs = ps.executeQuery();

        ResultSetMetaData metaData = rs.getMetaData(); //获取当前结果集的元数据
        int length = metaData.getColumnCount(); //可以获取当前结果集中字段总数
        while (rs.next()){
            //创建bean,映射结果集
            T bean = (T)c.newInstance();
            for (int i = 0 ; i < length ; i++){
                //获取指定列的字段名称
                String columnName = metaData.getColumnName(i + 1);
                //通过当前列名称获取获取当前行对应的值
                Object columnValue = rs.getObject(columnName);
                //通过反射把获取到的值映射到JavaBean中
                //获取属性对象
                Field field = c.getDeclaredField(columnName);
                //开权限
                field.setAccessible(true);
                //把值赋值给对应的bean对象
                field.set(bean,columnValue);
            }

            //把bean中的值存储到list集合中去
            list.add(bean);
        }
        this.close(conn,ps,rs);
        return list;
    }
}

database.properties

driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/kgc7_22
username = root
password = root123

JDBC API小结

两种思想

  • 面向接口编程的思想。

  • ORM思想(object relational mapping)对象关系映射。

    • 一个数据表对应一个java类

    • 表中的一条记录对应java类的一个对象

    • 表中的一个字段对应java类的一个属性

两种技术

  • JDBC结果集的元数据:ResultSetMetaData

    • 获取列数:getColumnCount()

    • 获取列的别名:getColumnLabel()

  • 通过反射,创建指定类的对象,获取指定的属性并赋值。

0

评论区