使用 JDBC 基本步骤
- 注册驱动
- 建立连接
- 创建 statement
- 执行 sql,得到 ResultSet
- 遍历结果集
- 释放资源
public class JDBCTest1 {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
//注册驱动
try {
//1.注册驱动
/*
* 对这行代码的理解:
* new Driver() 相当于创建一个类的实例,则必须先要加载这个类
* 这个类中有一个静态代码块
* static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
* 代码块中有这样一行代码
* DriverManager.registerDriver(new Driver());
* 在类加载的时候,就会执行静态代码块,也就执行这行代码
* 之后又执行代码 DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
* 相当于注册了两次驱动
* */
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
//Class.forName("com.mysql.jdbc.Driver");
//2.建立连接
//conn = DriverManager.getConnection("jdbc:mysql://localhost/student?serverTimezone=UTC","root","1183787376");
conn = JDBCUtil.geConn();
//3.创建statement
st = conn.createStatement();
String sql = "select * from t_stu";
rs = st.executeQuery(sql);
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
System.out.println(id + name + age);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtil.release(conn, st, rs);
}
}
}
public class JDBCUtil {
static String url = "jdbc:mysql://localhost/student?serverTimezone=UTC";
static String name = "root";
static String password = "1183787376";
public static Connection geConn(){
/*获取连接对象*/
Connection conn = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(url,name,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/*
* 释放资源
* */
public static void release(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;
}
}
private static void closeSt(Statement st){
try {
if(st != null){
st.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
st = null;
}
}
private static void closeConn(Connection conn){
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
}
使用 properties 配置文件
- 在 src 底下声明一个文件 xxx.properties ,内容如下:
driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost/student?serverTimezone=UTC
name=root
password=1183787376
-
在工具类中使用静态代码块读取属性
static{
try {
//创建一个属性配置对象
Properties properties = new Properties();
//InputStream is = new FileInputStream(“jdbc.properties”);//对应文件位于工程根目录
//使用类加载器去读取src下的资源
InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream(“jdbc.properties”);
properties.load(is);
//读取属性
driverClass = properties.getProperty(“driverClass”);
url = properties.getProperty(“url”);
name = properties.getProperty(“name”);
password = properties.getProperty(“password”);
} catch (IOException e) {
e.printStackTrace();
}
}
使用单元测试,测试代码
- 定义一个类,Testxxx,里面定义方法 testxxx
- 添加 junit 支持
- 在方法上面添加注解,其实就是一个标记
@Test
public void testQuery(){ }
Dao 模式
Data Access Object 数据访问对象
- 新建一个dao接口,里面声明数据库访问规则
public interface UserDao {
/*
* 查询所有
* */
void findAll();
}
- 新建一个dao实现类,具体实现先前定义的数据库访问规则
public class UserDaoImpl implements UserDao {
@Override
public void findAll() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtil.geConn();
st = conn.createStatement();
String sql = "select * from t_user";
rs = st.executeQuery(sql);
while(rs.next()){
String userName = rs.getString("username");
String passWord = rs.getString("password");
System.out.println(userName + " " +passWord);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(conn,st,rs);
}
}
}
- 直接使用实现
public class TestUserDaoImpl {
@Test
public void testFindAll(){
UserDao dao = new UserDaoImpl();
dao.findAll();
}
}
Statement 安全问题
- Statement 执行其实是拼接sql语句的。先拼接sql语句,然后在一起执行。
String sql = "select * from t_user where username='"+ userName +"' and password='" + passWord + "'" ;
UserDao dao = new UserDaoImpl();
dao.login("admin","1122234' or '1=1");
select * from t_user where username='admin' and password='12123124rrr' or '1=1'
前面先拼接sql语句,如果变量里面带有sql关键字,那么一并认为是关键字,不认为是普通字符串。向上面的密码"1122234' or '1=1"
中,不认为or也是密码的一部分,而认为是关键字。
- PrepareStatement
该对象就是替换前面的statement对象
@Override
public void login(String userName, String passWord) {
//一定是查询数据库,然后匹配给定的用户名和密码
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCUtil.geConn();
String sql = "select * from t_user where username=? and password=?" ;
//预先对sql语句执行语法检查,?对应的内容后面不管传递什么进来,都把它看作是字符串
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,userName);
ps.setString(2,passWord);
rs = ps.executeQuery();
if (rs.next()){
System.out.println("登陆成功");
}else{
System.out.println("登陆失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtil.release(conn,st,rs);
}
}