JDBC操作和访问数据库
- Statement
- PrepatedStatement
- CallableStatement
使用Statement存在弊端
- 需要拼写sql语句
- 存在sql注入的问题
解决方法:只要用PreparedStatement(从Statement扩展而来)取代Statement即可
JDBC中使用PreparedStatement插入数据
向customers表中添加一条记录
public void updateTest(){
Connection conn = null;
PreparedStatement ps = null;
try {
//1、读取配置文件
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//2、加载驱动
Class.forName(driverClass);
//3、获取连接
conn = DriverManager.getConnection(url, user, password);
//4、预编译sql语句,返回ProparedStatement的实例
String sql = "insert into customers(name,email,birth)values(?,?,?)";
ps = conn.prepareStatement(sql);
//5、填充占位符
ps.setString(1,"哪吒");
ps.setString(2,"nezha@163.com");
//处理sql中的date类型
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf.parse("1000-01-01");
ps.setDate(3,new java.sql.Date(date.getTime()));
//6、执行sql操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//7、资源的关闭
try {
if(ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
JDBCUtils工具类
因为在每次使用增删改查前都会存在连接数据库的操作,所以可以将连接数据库的操作封装为一个工具类
/**
* 操作数据库的工具类
*/
public class JDBCUtils {
public static Connection getConnection() throws Exception {
//1、读取配置文件,获取四个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//2、加载驱动
Class.forName(driverClass);
//3、获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* 关闭资源的操作
* @param conn
* @param ps
*/
public static void closeResource(Connection conn, Statement ps){
try {
if (ps != null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
通用的增删改操作,增删改操作的步骤相似,只有sql语句中的占位符不同,可以将增删改操作封装在一个方法内
public static void update(String sql,Object ...args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1、连接数据库
conn = getConnection();
//2、预编译sql语句,获取PreparedStatement实例
ps = conn.prepareStatement(sql);
//3、填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//4、执行
ps.execute();
//5、关闭资源
} catch (Exception e) {
e.printStackTrace();
} finally {
closeResource(conn,ps);
}
}