JDBC02

对数据库操作工具的基本封装

针对于查询语句的封装

public class TestQuery {
public static void main(String[] args) throws Exception {
Connection conn = DButil.getConnection();
Statement stmt = conn.createStatement();
String sql = "select * from account";
DBObject dbObject = executeQuery(sql);
ResultSet rs = dbObject.getRs();
while(rs.next()){//conn.close(); 连接关闭结果集也会关闭
// 所以应该在循环结束之后再关闭
int id = rs.getInt(1);
String name = rs.getString(2);
double balance = rs.getDouble(3);
System.out.println("id:" + id + " name:" + name + " balance:" + balance);
}
executeQuery(sql);
close(dbObject);
stmt.close();
conn.close();
rs.close();
}
public static DBObject executeQuery(String sql) throws SQLException, ClassNotFoundException {
Connection conn = DButil.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
return new DBObject(conn,stmt,rs);
}
public static void close(DBObject dbObject) throws SQLException {
dbObject.getRs().close();
}
}

public class DBObject {
private Connection conn;
private Statement stmt;
private ResultSet rs;

public Connection getConn() {
return conn;
}

public void setConn(Connection conn) {
this.conn = conn;
}

public Statement getStmt() {
return stmt;
}

public void setStmt(Statement stmt) {
this.stmt = stmt;
}

public ResultSet getRs() {
return rs;
}

public void setRs(ResultSet rs) {
this.rs = rs;
}

public DBObject(Connection conn, Statement stmt, ResultSet rs) {
this.conn = conn;
this.stmt = stmt;
this.rs = rs;
}
}

sql注入攻击

PreparedStatement

是statement的子接口，不采用拼接sql的方式，而先占位问号，再采用传入参数的方式进行

public class LoginPreparedTest {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Scanner sc = new Scanner(System.in);
Connection con = DButil.getConnection();
String sql = "select * from admin where name = ? and password = ?";
System.out.println("请输入用户名");
String name = sc.next();
System.out.println("请输入密码");
String pwd = sc.next();
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, pwd);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
rs.close();
ps.close();
}
}

封装PrepareStatement操作

public static int executeUpdate(String sql, Object…args) throws ClassNotFoundException, SQLException {
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
return ps.executeUpdate();
}

PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, pwd);
// 等价于
DButil.executeUpdate(sql,name,pwd);

UUID

简介:University Unique identify:通用唯一识别码

MySQL数据库类型和java类型的对应关系

getString()是通用方法，但是最后获取的就是字符串