jdbc学习笔记2
statement（使用Java操作数据库）
Statement
上一篇讲到，使用connection建立数据库连接，接下来使用statement类操作数据库实现数据库的增删改查
但是statement有一个安全问题
代码实现如下
String sql = "select * from users where username= '"+键入内容+"' and userpwd='"+键入内容+"'";
当有人恶意攻击数据库时，只需添加特殊的sql语句就可以完成对数据库进行增删改查
例如：
键入 ‘1’ 第二个位置键入‘1’ or ‘1’=‘1’；此时sql语句恒为判断正确，输出查询内容具有巨大的安全隐患
把这种过程称为sql注入
故一般不使用statement，使用perparedstatement
PreparedStatement
相对于Statement而言PerparedStatement类更安全，其中为了防止sql注入实现了sql的预编译
sql预编译（动态sql语句）
我理解为将所写的sql格式进行实现告知数据库，随后只添加数据，再添加完数据后，数据库，不会将占位符内的内容认定为sql语句，则不会出现sql注入的风险
insert into customers(name,email,birth)value (?,?,?)//添加数据内容待指定
其中问号为占位符
使用PerparedStatement操作数据库
代码实现CRUD-INSERT
public class Preparedstatementtest {
//preparedstatement
@Test
public void test() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
1,建立数据库连接
InputStream resourceAsStream = Preparedstatementtest .class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties=new Properties();
properties.load(resourceAsStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);//注册驱动
connection = DriverManager.getConnection(url, user, password);//建立连接
2.预编译sql
String sql="insert into customers(name,email,birth)value (?,?,?)";
3.为占位符添加信息
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,"吴");
preparedStatement.setString(2,"w.com");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date parse = simpleDateFormat.parse("1990-10-09");
preparedStatement.setString(3, String.valueOf(new java.sql.Date(parse.getTime())));
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
4.关闭连接器和操作流
if(preparedStatement!=null)
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}}
代码实现CRUD-UPDATE
首先将，注册驱动和创建连接进行包装成工具类
代码如下：
public class JdbcUtils {
public static Connection getConnection() throws Exception {
//湖区连接
InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties=new Properties();
properties.load(resourceAsStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
public static void closeResourse(Connection connection,PreparedStatement preparedStatement ){
//关闭资源
try {
if(preparedStatement!=null)
preparedStatement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection!=null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
接下来实现修改数据库中的记录
代码如下：
@Test
public void Updatetest2() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
//1.获取连接
connection = JdbcUtils.getConnection();
// 2.预编译sql语句
String sql="update customers set name = ? where id = ?";
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
preparedStatement.setString(1,"莫扎特");
preparedStatement.setInt(2,18);
//执行
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//4.关闭资源
JdbcUtils.closeResourse(connection,preparedStatement);
}
}
代码实现CRUD-DELETE
首先完成对增删改的统一抽象方法实现
public void update(String sql,Object ...args){
//获取连接
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JdbcUtils.getConnection();
//预编译sql语句
preparedStatement = connection.prepareStatement(sql);
//填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
//执行操作
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭资源
JdbcUtils.closeResourse(connection,preparedStatement);
}
}
代码实现删除表中数据
@Test
public void deletetest(){
String sql="delete from customers where id=?";
update(sql,19);
}
JDBC代码实现select
首先包装一个数据表的类
import java.util.Date;
public class Customertest {
private int id;
private String name;
private String email;
private Date brith;
public Customertest() {
}
public Customertest(int id, String name, String email, Date brith) {
this.id = id;
this.name = name;
this.email = email;
this.brith = brith;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBrith() {
return brith;
}
public void setBrith(Date brith) {
this.brith = brith;
}
@Override
public String toString() {
return "Customertest{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", brith=" + brith +
'}';
}
}
实现查询并输出结果集
@Test
public void selecttest(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConnection();//创建连接
String sql ="select id,name,email,birth from customers where id=?";//预编译sql语句
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1,1);//填充占位符
resultSet = preparedStatement.executeQuery();//执行输出结果集
//下面为操作结果集
if (resultSet.next()){//判断结果集的下一个位置是否有数据，如果有数据返回true，自动下移，如果没有数据返回flase
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
java.sql.Date birth = resultSet.getDate(4);
Customertest customertest = new Customertest(id, name, email, birth);
System.out.println(customertest);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.closeResourse2(connection, preparedStatement, resultSet);
}
}
结果为
实现对某个表通用的查询
封装查询customer表的方法
public Customertest queryfrocustomer(String sql,Object...args){
Connection connection= null;
PreparedStatement preparedStatement = null;
ResultSet resultSet= null;
try {
connection = JdbcUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集的元数据
int columnCount = metaData.getColumnCount();//获取结果集的列数
if (resultSet.next()){
Customertest customertest=new Customertest();
for (int i = 0; i < columnCount; i++) {
Object object = resultSet.getObject(i + 1);
//获取每个列的列名给对象赋值
String catalogName = metaData.getCatalogName(i + 1);
Field declaredField = Customertest.class.getDeclaredField(catalogName);
declaredField.setAccessible(true);
declaredField.set(customertest,object);
}
return customertest;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.closeResourse2(connection,preparedStatement,resultSet);
}
return null;
}
@Test
public void testquery(){
String sql ="select id,name from customers where id=?";
Customertest qu = queryfrocustomer(sql, 2);
System.out.println(qu);
}
针对不同表的查询操作
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class PreparedStatementQueryTest {
@Test
public void test(){
String sql="select id,name from customers where id=?";
Customertest getinstance = getinstance(Customertest.class, sql, 13);
System.out.println(getinstance);
}
public <T> T getinstance(Class<T> clazz,String sql ,Object...args){
Connection connection= null;
PreparedStatement preparedStatement = null;
ResultSet resultSet= null;
try {
connection = JdbcUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i+1,args[i]);
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();//获取结果集的元数据
int columnCount = metaData.getColumnCount();//获取结果集的列数
if (resultSet.next()){
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object object = resultSet.getObject(i + 1);
//获取每个列的列名给对象赋值
String catalogName = metaData.getCatalogName(i + 1);
Field declaredField = Customertest.class.getDeclaredField(catalogName);
declaredField.setAccessible(true);
declaredField.set(t,object);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.closeResourse2(connection,preparedStatement,resultSet);
}
return null;
}
}
总结
使用statement类对数据库进行操作，增删改查crud以及远离加进阶代码实现，包含了反射，泛型等技术
剑谱最终页，无爱即是神
下篇内容学习操作blob类型字段，以及批量插入
1462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
