jdbc总结

第一,jdbc是什么?
jdbc是sun提供的用于操作数据库的接口,各个数据库厂商实现接口,我们会调用就行了,操作数据库就导入提供的架包.
第二,案例查询操作
public static void main(String[] args) throws Exception {
//加载驱动
DriverManager.registerDriver(new Driver());
//创建连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "123");
//编写sql
String sql = "select * from user";
//执行sql
//得到statement
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
//遍历结果集得到每条记录
while(rs.next()) {
int id = rs.getInt("id");
String username = rs.getString("username");
int chinese = rs.getInt("chinese");
int english = rs.getInt("english");
System.out.println(id+" :: "+username+" :: "+chinese+" :: "+english);
}
//释放资源
rs.close();
stmt.close();
conn.close();
}

第三,DriverManager是什么?
DriverManager是java.sql的类
new Driver()是数据库驱动,唯一的其他类.


第四,Connection 是什么?
createStatement
prepareStatement,用于防止注入,一般开发用,安全


第五,Statement 是什么?
返回查询的结果集 ResultSet executeQuery(String sql) 


返回成功的记录数 int executeUpdate(String sql) 


返回是布尔类型，如果执行的是查询的操作返回true，否则返回的false boolean execute(String sql) 


第六,ResultSet 是什么?
next(); 向下走
getString();数据库具体的值,对应的名称
getInt();
getObject();


第七,释放资源,sun公司的官方代码
finally {
//释放资源
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}

if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}

if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}


第八,crud操作
executeQuery(sql) 查


executeUpdate(sql) 增删改


第九,工具类
操作数据库相同的代码是注册驱动,关闭资源,抽取工具类.
读取 Properties 文件2种方法
Properties p = new Properties();
InputStream in = new FileInputStream("src/db.properties");
p.load(in);
String drivername = p.getProperty("drivername");
String url = p.getProperty("url");
String username = p.getProperty("username");
String password = p.getProperty("password");


ResourceBundle bundle = ResourceBundle.getBundle("db");
String drivername = bundle.getString("drivername");
String url = bundle.getString("url");
String username = bundle.getString("username");
String password = bundle.getString("password");


public static Connection getConnection() throws Exception {
Class.forName(drivername);
Connection connection = DriverManager
.getConnection(url, user, password);
return connection;
}


第十,工具类实现crud
public static void clearConn(Connection connection, Statement statement,
ResultSet resultSet) {


if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {


}
resultSet = null;
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {


}
statement = null;
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {


}
connection = null;
}
}
第十一,sql的注入
注入前
//SELECT * FROM stu WHERE username='luanxie' OR ('1=1' AND PASSWORD='456')(预编译之前)
还是可以查询数据库.'1=1'是字符串默认为true,'1'='1'是逻辑判断是true


用预编译PreparedStatement防止注入,用占位符
String sql = "select * from user where username=? and password=?";
注入后
//SELECT * FROM stu WHERE username='luanxie\' OR \'1=1'  AND PASSWORD='456'(预编译之后)
查询不了

第十二,
预编译对象executeQuery()执行查询语句
String sql = "select * from user where username=? and password=?";


预编译对象executeUpdate()执行增,删,改的语句
String sql = "INSERT INTO stu VALUES(?,?,?)";

String sql = "DELETE FROM stu WHERE id=?";


String sql = "UPDATE stu SET username=? ,PASSWORD=? WHERE id =?";