/*
1、注册驱动Class.forName("驱动名")
2、获得连接:Conecton conn = DriverManager.getConnection(“数据库服务路径”,“用户名”,“密码”)
3、创建执行Sql 语句的Statement对象
Statement statement = conn.createStatement();
4、使用statement执行sql语句
4.1 执行增删改的sql语句 : int num = statement.executeUpdate(sql); //返回值表示受到影响的行数
4.2 执行查询的sql语句:
ResultSet rst = statement.executeQuery(sql); //返回查询的结果集
5、如果第四步查询的是结果集,需要将查询的结果集遍历出来
6、关闭资源,后创建的资源先关闭
rst.close();
statement.close();
conn.close();
*/
public class TestJDBC {
//将一个变量变成成员变量快捷键:选中变量,ctrl+alt+F,回车
private Statement statement;
private Connection conn;
@Before
public void init() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
//String url = "jdbc:mysql://服务器主机地址:端口号/数据库名?characterEncoding=utf8"
String url = "jdbc:mysql://localhost:3306/day18?characterEncoding=utf8";
String user= "root";
String password="123456";
//快捷键:alt+enter
conn = DriverManager.getConnection(url, user, password);
statement = conn.createStatement();
}
@Test
public void testFindAll() throws Exception {
String sql = "select * from user";
ResultSet rst = statement.executeQuery(sql);
while (rst.next()){
int id = (int)rst.getObject("id");
String username = (String)rst.getObject("username");
String psd = (String)rst.getObject("password");
String nickname = (String)rst.getObject("nickname");
System.out.println(id+","+username+","+psd+","+nickname);
System.out.println("--------------------------------");
}
rst.close();
}
@After
public void destroy() throws Exception {
statement.close();
conn.close();
}
@Test
public void testInsert() throws Exception {
String sql ="insert into user (username,password,nickname) values ('ab','123456','ac')";
int i = statement.executeUpdate(sql);
System.out.println(i);
}
@Test
public void testDelete() throws SQLException {
String sql = "delete from user where id=1";
int i = statement.executeUpdate(sql);
System.out.println(i);
}
@Test
public void testUpdate() throws Exception{
String sql = "update user set password='111111' where id=2";
int i = statement.executeUpdate(sql);
System.out.println(i);
}
//查询id为2的用户的信息
@Test
public void testFindById() throws SQLException {
String sql ="select * from user where id=2";
ResultSet rst = statement.executeQuery(sql);
List<User> userList = new ArrayList<>();
while(rst.next()){
int id = (int)rst.getInt("id");
String name = rst.getString("username");
String password = rst.getString("password");
String nickname = rst.getString("nickname");
System.out.println(id+","+name);
//从数据库中查询数据的目的是:为了在java代码内存中操作查询的数据。
// 需要将查询出来的数据作为一个整体:将查询出来的数据存储到一个map中或user对象中
//再创建一个user类
User user = new User(id, name, password, nickname);
userList.add(user);
}
System.out.println(userList);
}
}
1、JDBC概述
代表一组独立于任何数据库管理系统的API,声明在java.sql和javax.sql包中,是SUN公司提供的一组接口规范。
2、JDBC使用步骤
(1)注册驱动Class.forName("驱动名") (2)获得连接:Conecton conn = DriverManager.getConnection(“数据库服务路径”,“用户名”,“密码”) (3)创建执行Sql 语句的Statement对象 Statement statement = conn.createStatement(); (4)使用statement执行sql语句 (4.1) 执行增删改的sql语句 : int num = statement.executeUpdate(sql); //返回值表示受到影响的行数 (4.2) 执行查询的sql语句: ResultSet rst = statement.executeQuery(sql); //返回查询的结果集 (5)如果第四步查询的是结果集,需要将查询的结果集遍历出来(详见代码段) (6)关闭资源,后创建的资源先关闭 rst.close(); statement.close(); conn.close();
注:这里测试用到两个jar包:hamcrest-core-1.3.jar 和 junit-4.12.jar
二、但是使用Statement会出现sql注入问题。
sql注入:由于sql语句中字符串拼接,导致sql语句的格式发生变化而引发的问题
解决方法:使用preparedStatement(主要区别在步骤 3, 4, 5)
public class TestPreparedStatement {
private Connection conn;
@Before
public void init() throws Exception {
//注:预编译语句不能放在此方法中,因为每次预编译内容不同
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///day18?characterEncoding=utf8", "root", "123456");
}
@Test
public void testlogin() throws Exception {
//3、预编译参数化的sql语句
String username = "ww";
String password = "123456";
//3.1 编写参数化的sql语句,即需要传入参数的地方使用?占位
String sql="select * from user where username=? and password=?";
//3.2 预编译sql语句,可以确定sql语句的结构,预编译之后就无法再通过sql注入改变sql语句的结构
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//4、给问号占位符传入对应参数
preparedStatement.setObject(1,username);
preparedStatement.setObject(2,password);
//5、执行sql语句,此时不再传入sql语句,因为预编译的时候已经传入了
ResultSet rst= preparedStatement.executeQuery();
while (rst.next()){
int id = (int)rst.getObject("id");
String username1 = (String)rst.getObject("username");
String psd = (String)rst.getObject("password");
String nickname = (String)rst.getObject("nickname");
System.out.println(id+","+username1+","+psd+","+nickname);
System.out.println("--------------------------------");
}
rst.close();
preparedStatement.close();
}
@After
public void destroy() throws Exception {
conn.close();
}
@Test
public void testInsert() throws Exception {
String sql = "insert into user(username,password,nickname) values(?,?,?)";
//预编译
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//设置占位符处的参数
preparedStatement.setObject(1,"dd");
preparedStatement.setObject(2,"123456");
preparedStatement.setObject(3,"ddd");
//执行sql语句
int i = preparedStatement.executeUpdate();
System.out.println(i);
preparedStatement.close();
}
}
三、批量处理数据
/*测试批量添加
1、获得连接处参数设置
2、设置参数时要注意将批量数据执行添加操作preparedStatement.addBatch();
3、执行批量操作用的方法:preparedStatement.executeBatch();
*/
public class TestBatch {
@Test
public void testBatchInsert() throws Exception {
//测试批量添加
Class.forName("com.mysql.jdbc.Driver");
//获得链接
Connection conn = DriverManager.getConnection("jdbc:mysql:///day18?characterEncodingutf8&rewriteBatchedStatements=true", "root", "123456");
//预编译sql语句
String sql = "insert into user(username,password,nickname) values (?,?,?)";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//设置参数
for(int i=1;i<=30;i++){
preparedStatement.setObject(1,"jay_"+i);
preparedStatement.setObject(2,"123456"+i);
preparedStatement.setObject(3,"jay_"+i);
//添加到批量操作中
preparedStatement.addBatch();
}
//执行批量操作,这里用的方法是executeBatch()
preparedStatement.executeBatch();
preparedStatement.close();
conn.close();
}
}
四、使用JDBC的事务完成转账案例
public class TestTransaction {
@Test
public void testTransfer() throws Exception {
//测试转账
//1、注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2、获得连接
Connection conn = DriverManager.getConnection("jdbc:mysql:///day18?characterEncoding=utf8","root","123456");
//预编译sql语句
String sql="update account set money=money+? where name=?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
//开启事务
conn.setAutoCommit(false);
//快捷键选定try/catch:ctrl+alt+T
try {
//3.1zs扣款500
preparedStatement.setObject(1,-500);
preparedStatement.setObject(2,"zs");
preparedStatement.executeUpdate();
//3.2 l收款500
preparedStatement.setObject(1,500);
preparedStatement.setObject(2,"ls");
preparedStatement.executeUpdate();
//提交事务
conn.commit();
} catch (Exception throwables) {
throwables.printStackTrace();
conn.rollback();
}finally {
conn.setAutoCommit(true);
}
preparedStatement.close();
conn.close();
}
}