Java实现关系型数据库工具类JdbcUtils系列四：PreparedStatement执行sql语句实现查询

• 一、建表语句
• 二、JdbcUtils实现建立连接和关闭连接
• 三、PreparedStatement实现查询

CREATE TABLE `stuinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生自增id',
  `stunum` int(11) NOT NULL COMMENT '学号',
  `name` varchar(100) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `hobby` varchar(300) NOT NULL COMMENT '爱好',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_stunum` (`stunum`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtils {

    
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        Properties props = new Properties();
        props.load(JdbcUtils.class.getClassLoader().getResourceAsStream("application" +
                ".properties"));

        String driverClassName = props.getProperty("driverClassName");
        String url = props.getProperty("url");
        String user = props.getProperty("username");
        String password = props.getProperty("password");

        Class.forName(driverClassName);

        //2.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);

        return conn;
    }


    public static void release(Connection conn) throws SQLException {
        conn.close();
    }


    public static void release(Connection conn, PreparedStatement ps) throws SQLException {
        ps.close();
        conn.close();
    }

    public static void release(Connection conn, PreparedStatement ps, java.sql.ResultSet rs) throws SQLException {
        rs.close();
        ps.close();
        conn.close();
    }
}

import org.junit.Test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class PrepareStatementTest {
    @Test
    public void test2() throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            //1.获取连接
            conn = JdbcUtils.getConnection();

            //2.通过当前连接，获取PrepareStatement，用于发送sql
            String sql = "select stunum,name,age,hobby,create_time from dw.stuinfo where stunum != ?";
            ps = conn.prepareStatement(sql);

            //3.填充占位符
            ps.setInt(1,100010);

            //4.执行sql
            //ResultSet结果集
            rs = ps.executeQuery();

            //5.获取结果集中的数据
            while (rs.next()){ //移动结果集中记录到下一行，若有下一行则返回true，没有下一行则返回false
                //根据列的索引获取对应列的数据
                int stunum = rs.getInt(1);
                String name = rs.getString(2);
                int age = rs.getInt(3);
                String hobby = rs.getString(4);
                Date create_time = rs.getDate(5);

                System.out.println(stunum + "," + name + "," + age + "," + hobby + "," + create_time);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,ps,rs);
        }
    }
}