一、建表陳述句

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='學生資訊表';

二、JdbcUtils實作建立連接和關閉連接

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 {

    /**
     * 獲取連接
     * @return
     * @throws IOException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    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();
    }
}

三、PreparedStatement實作查詢

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;

/*
    使用PrepareStatement完成查詢
*/
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);
        }
    }
}