JDBC学习笔记
一、jdbc使用步骤
1)注册驱动:
DriverManager. registerDriver ( new com. mysql. jdbc. Driver ( ) ) ;
2)建立连接:参数1:协议+访问的数据库 参数2:用户名 参数3:密码
conn = DriverManager. getConnection ( "jdbc:mysql://localhost/student" , "root" , "123456" ) ;
3)创建Statement
st = conn. createStatement ( ) ;
4)执行Sql语句,得到ResultSet
String sql = "select * from t_student" ;
rs = st. executeQuery ( sql) ;
5)遍历结果
while ( rs. next ( ) ) {
int id = rs. getInt ( "id" ) ;
String name = rs. getString ( "name" ) ;
int age = rs. getInt ( "age" ) ;
System. out. println ( "id:" + id + " name:" + name + " age:" + age) ;
}
6)释放资源
if ( rs != null) {
rs. close ( ) ;
}
} catch ( SQLException e) {
e. printStackTrace ( ) ;
} finally {
rs = null;
}
. . .
二、JDBC 工具类构建
1)资源释放工作整合
public static void releaseResouce ( Connection conn, Statement st, ResultSet rs) {
closeRs ( rs) ;
closeSt ( st) ;
closeConn ( conn) ;
}
private static void closeRs ( ResultSet rs) {
try {
if ( rs != null) {
rs. close ( ) ;
}
} catch ( SQLException e) {
e. printStackTrace ( ) ;
} finally {
rs = null;
}
}
. . .
2)驱动防二次加载
DriverManager. registerDriver ( new com. mysql. jdbc. Driver ( ) ) ;
Driver 这个类里面有静态代码块,被加载时就会被加载,所以等同于注册了两次驱动,其实没这个必要
try {
Class. forName ( "com.mysql.jdbc.Driver" ) ;
} catch ( ClassNotFoundException e) {
e. printStackTrace ( ) ;
}
3)使用properties配置文件
(1)在src底下声明一个文件jdbc.properties,内容如下:
driverClass = com. mysql. jdbc. Driver
url = jdbc: mysql: / / localhost/ student
user = root
password = 123456
(2)在工具类中使用静态代码块读取属性
static {
try {
Properties properties = new Properties ( ) ;
InputStream is = JdbcUntil. class . getClassLoader ( ) . getResourceAsStream ( "jdbc.properties" ) ;
properties. load ( is) ;
driverClass = properties. getProperty ( "driverClass" ) ;
url = properties. getProperty ( "url" ) ;
user = properties. getProperty ( "user" ) ;
password = properties. getProperty ( "password" ) ;
} catch ( IOException e) {
e. printStackTrace ( ) ;
}
}
Java类加载器(英语:Java Classloader)是Java运行时环境(Java Runtime Environment)的一部分,负责动态加载Java类到Java虚拟机的内存空间中。
三、数据库的CURD sql
insert insert into student ( name, age) values ( 'zhangsan' , 18 ) ;
insert into student values ( null , 'zhangsan' , 18 ) ;
try {
conn = JdbcUntil. getConn ( ) ;
st = conn. createStatement ( ) ;
String sql = "insert into t_student (name,age) values ('aobanma',18)" ;
int rs = st. executeUpdate ( sql) ;
if ( rs > 0 ) {
System. out. println ( "添加成功" ) ;
} else {
System. out. println ( "添加失败" ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, st) ;
}
delete delete from student where id = 1 ;
try {
conn = JdbcUntil. getConn ( ) ;
st = conn. createStatement ( ) ;
String sql = "delete from t_student where name = 'aobanma'" ;
int rs = st. executeUpdate ( sql) ;
if ( rs > 0 ) {
System. out. println ( "删除成功" ) ;
} else {
System. out. println ( "删除失败" ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, st) ;
}
query select * from student;
try {
conn = JdbcUntil. getConn ( ) ;
st = conn. createStatement ( ) ;
String sql = "select * from t_student" ;
rs = st. executeQuery ( sql) ;
while ( rs. next ( ) ) {
String name = rs. getString ( "name" ) ;
int age = rs. getInt ( "age" ) ;
System. out. println ( name+ " " + age) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, st, rs) ;
}
update update student set age = 18 where id = 1 ;
try {
conn = JdbcUntil. getConn ( ) ;
st = conn. createStatement ( ) ;
String sql = "update t_student set age = 20 where id = 1" ;
int rs = st. executeUpdate ( sql) ;
if ( rs > 0 ) {
System. out. println ( "更新成功" ) ;
} else {
System. out. println ( "更新失败" ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, st) ;
}
四、使用单元测试,测试代码
1.定义一个类TestXXX,定义一个方法testXXX;
2.添加junit的支持;
右键工程 ----> add Library ----> Junit ----> Junit4
3.在方法的上面加上注解,其实就是一个标记 ;
@Test
public void testQuery ( ) {
. . . .
}
4.运行:
1.光标选中方法名字,右键执行单元测试;
2.打开outline视窗,选择方法右键执行.
五、Dao模式
Data Acess Object 数据访问对象
1.新建一个dao的接口,里面声明数据库访问规则
public interface UserDao {
void query ( ) ;
}
2.新建一个dao的实现类,具体实现早前定义的规则
public void query ( ) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUntil. getConn ( ) ;
st = conn. createStatement ( ) ;
String sql = "select * from t_user" ;
rs = st. executeQuery ( sql) ;
while ( rs. next ( ) ) {
String username = rs. getString ( "username" ) ;
String passwd = rs. getString ( "passwd" ) ;
System. out. println ( username+ " " + passwd) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, st, rs) ;
}
}
3.直接使用实现
@Test
public void testQuery ( ) {
UserDao dao = new UserDaoImpl ( ) ;
dao. query ( ) ;
}
六、Statement安全问题
1.Statement执行,其实是拼接sql语句的
String sql = "select * from t_user where username ='" + username + "' and passwd = '" + passwd + "'" ;
UserDao dao = new UserDaoImpl ( ) ;
dao. login ( "zhangsan" , "10088 ' or '1=1" ) ;
rs = st. executeQuery ( sql) ;
2.PrepareStatement
该对象就是替换前面的Statement对象。
1.相比较以前的Statement,预先处理给定的sql语句,对其执行语法检查。在sql语句里面使用?占位符来代替后续要传递进来的变量。后面进来的变量值,将会被看成是字符串,不会产生任何的关键字。
try {
conn = JdbcUntil. getConn ( ) ;
String sql = "insert into t_user values(null,?,?)" ;
ps = conn. prepareStatement ( sql) ;
ps. setString ( 1 , username) ;
ps. setString ( 2 , passwd) ;
int result = ps. executeUpdate ( ) ;
if ( result > 0 ) {
System. out. println ( "添加成功" ) ;
} else {
System. out. println ( "添加失败" ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, ps) ;
}
try {
conn = JdbcUntil. getConn ( ) ;
String sql = "delete from t_user where id = ?" ;
ps = conn. prepareStatement ( sql) ;
ps. setInt ( 1 , id) ;
int result = ps. executeUpdate ( ) ;
if ( result > 0 ) {
System. out. println ( "删除成功" ) ;
} else {
System. out. println ( "删除失败" ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, ps) ;
}
try {
conn = JdbcUntil. getConn ( ) ;
String sql = "select * from t_user where username = ? and passwd = ?" ;
ps = conn. prepareStatement ( sql) ;
ps. setString ( 1 , username) ;
ps. setString ( 2 , passwd) ;
rs = ps. executeQuery ( ) ;
if ( rs. next ( ) ) {
System. out. println ( "登录成功" ) ;
} else {
System. out. println ( "登录失败" ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, ps, rs) ;
}
try {
conn = JdbcUntil. getConn ( ) ;
String sql = "update t_user set username = ? where id = ?" ;
ps = conn. prepareStatement ( sql) ;
ps. setString ( 1 , username) ;
ps. setInt ( 2 , id) ;
int result = ps. executeUpdate ( ) ;
if ( result > 0 ) {
System. out. println ( "修改成功" ) ;
} else {
System. out. println ( "修改失败" ) ;
}
} catch ( Exception e) {
e. printStackTrace ( ) ;
} finally {
JdbcUntil. releaseResouce ( conn, ps) ;
}
七、总结
1.jdbc
2.抽取工具类
3.Statement CRUD
演练curd
4.Dao模式
5.PrepareStatement CRUD
预处理sql语句,解决上面statement出现的问题