基本使用流程
- 在src同级目录创建lib文件夹,把mysql驱动包添加进去并build path
2.以下式代码基本使用流程
package com.tjsmc.view;
import java.security.interfaces.RSAKey;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.Driver;
public class One {
public static void main(String[] args) {
Connection connection=null;
Statement createStatement=null;
ResultSet executeQuery=null;
//ctrl+t 鼠标移动到对应的单词,ctrl+t显示继承关系
try {
//1.注册驱动
DriverManager.registerDriver(new Driver());
//2.建立连接 ctrl+1自动完成变量
connection = DriverManager.getConnection("jdbc:mysql://localhost/java", "root", "zengqiang");
//3.创建Statement 和数据库交互必须有Statement
createStatement = connection.createStatement();
String sql="select * from student";
//4.执行查询
executeQuery = createStatement.executeQuery(sql);
while (executeQuery.next()) {
String name = executeQuery.getString("name");
String sex = executeQuery.getString("sex");
int age=executeQuery.getInt("age");
System.out.println(name+"..."+age+"..."+sex);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//关闭和创建逆向
if (executeQuery!=null) {
try {
executeQuery.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (createStatement!=null) {
try {
createStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection!=null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
JDBC简单工具类
package com.tjsmc.view;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCUtil {
static String driverClass=null;
static String url=null;
static String name=null;;
static String password=null;
//静态代码块,随着类的加载而加载
static{
Properties properties=new Properties();
//使用类加载器获取src目录下的资源文件
// InputStream iStream=JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
//下面等效,获取项目根目录下的资源文件(所以虽然两种都可以加载资源文件,但是资源文件对应的地址不同)
InputStream iStream=null;
try {
iStream = new FileInputStream("jdbc.properties");
} catch (FileNotFoundException e1) {
e1.printStackTrace();
}
try {
properties.load(iStream);
driverClass=properties.getProperty("driverClass");
url=properties.getProperty("url");
name=properties.getProperty("name");
password=properties.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return
*/
public static Connection getConn() {
Connection conn=null;
try {
//从jdbc4.0开始注册驱动可以不注册,下面一句代码可省略
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url,name,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/**
* 释放资源
* @param conn
* @param st
* @param rs
*/
public static void release(Connection conn,Statement st,ResultSet rs) {
closeConn(conn);
closeRs(rs);
closeSt(st);
}
/**
* 释放资源的重载
* @param conn
* @param st
*/
public static void release(Connection conn,Statement st) {
closeConn(conn);
closeSt(st);
}
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;
}
}
}
jdbc.properties文件:
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/java
name=root
password=zengqiang
单元测试
- 右键项目,build path
- add libraries条目
- 选择junit4
- 新建test类或者规划包
- 在要进行单元测试的方法上面加上@Test
- 选中方法名右键 run junit test即可
package com.tjsmc.view.test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import com.tjsmc.view.JDBCUtil;
public class TestDemo {
@Test
public void testQuery() {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql="select * from student";
rs = st.executeQuery(sql);
while (rs.next()) {
String name = rs.getString("name");
String sex = rs.getString("sex");
int age=rs.getInt("age");
System.out.println(name+"..."+age+"..."+sex);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st, rs);
}
}
@Test
public void testInsert() {
Connection conn=null;
Statement st=null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql="insert into student values('彭',20,'1')";
int executeUpdate = st.executeUpdate(sql);
if (executeUpdate>0) {
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st);
}
}
@Test
public void testDelete() {
Connection conn=null;
Statement st=null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql="delete from student where name='彭'";
int executeUpdate = st.executeUpdate(sql);
if (executeUpdate>0) {
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st);
}
}
@Test
public void testUpdate() {
Connection conn=null;
Statement st=null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql="update student set age=100 where name='强'";
int executeUpdate = st.executeUpdate(sql);
if (executeUpdate>0) {
System.out.println("更新成功");
}else{
System.out.println("更新失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st);
}
}
}
Statement隐患
statement执行时先拼接字符串然后执行sql语句,有安全隐患。
基本案例如下:
@Test
public void testQuery() {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql="select * from student where name='曾' and age=5";
rs = st.executeQuery(sql);
if (rs.next()) {
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st, rs);
}
}
结果是:登录失败
安全隐患案例:
@Test
public void testQuery() {
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
//此处是唯一区别
String sql="select * from student where name='曾' and age=5 or 1=1";
rs = st.executeQuery(sql);
if (rs.next()) {
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st, rs);
}
}
结果是:登录成功,因为全面条件不论怎么样,但是1=1是永远成立的
登录注册案例:
@Test
public void testQuery() {
String name="assa";
String age="20 or 1=1";
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = JDBCUtil.getConn();
st = conn.createStatement();
String sql="select * from student where name='"+name+"' and age="+age;
rs = st.executeQuery(sql);
//查询登录一般结果只有一个或者没有
if (rs.next()) {
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, st, rs);
}
}
PrepareStatement
预编译的Statement用于解决Statement的安全问题,通过占位符进行预编译,索引从1开始
单元测试实例类:
package com.tjsmc.view.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
import com.tjsmc.view.JDBCUtil;
public class TestDemo {
@Test
public void testQuery() {
String name="曾";
String sex="30 or 1=1";
// String sex="30";
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn = JDBCUtil.getConn();
String sql="select * from student where name=? and sex=?";
ps = conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, sex);
////注意此处executeQuery内部已经没有传参了
rs = ps.executeQuery();
if (rs.next()) {
System.out.println("登录成功");
}else{
System.out.println("登录失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps, rs);
}
}
@Test
public void testInsert() {
Connection conn=null;
PreparedStatement ps=null;
try {
conn = JDBCUtil.getConn();
String sql="insert into student values(?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "彭");
ps.setInt(2,20);
ps.setString(3, "3");
//注意此处executeUpdate内部已经没有传参了
int executeUpdate = ps.executeUpdate();
if (executeUpdate>0) {
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
@Test
public void testDelete() {
Connection conn=null;
PreparedStatement ps=null;
try {
conn = JDBCUtil.getConn();
String sql="delete from student where name=?";
ps = conn.prepareStatement(sql);
ps.setString(1, "彭");
int executeUpdate = ps.executeUpdate();
if (executeUpdate>0) {
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
@Test
public void testUpdate() {
Connection conn=null;
PreparedStatement ps=null;
try {
conn = JDBCUtil.getConn();
String sql="update student set age=200 where name=?";
ps = conn.prepareStatement(sql);
ps.setString(1, "强");
int executeUpdate = ps.executeUpdate();
if (executeUpdate>0) {
System.out.println("更新成功");
}else{
System.out.println("更新失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.release(conn, ps);
}
}
}
网友评论