Java实现关系型数据库工具类JdbcUtils系列四:PreparedStatement执行sql语句实现查询
一、建表语句
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 {
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);
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;
public class PrepareStatementTest {
@Test
public void test2() throws SQLException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
String sql = "select stunum,name,age,hobby,create_time from dw.stuinfo where stunum != ?";
ps = conn.prepareStatement(sql);
ps.setInt(1,100010);
rs = ps.executeQuery();
while (rs.next()){
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);
}
}
}