JDBC
JDBC固定步骤
- 加载驱动
- 连接数据库
- 向数据库发送SQL对象 Statement
- 编写sql
- 执行sql
- 关闭连接
xml配置
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
JDBC 查询
package com.srx;
import java.sql.*;
public class TestJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
String url="jdbc:mysql://localhost:3306/lx?useUnicode=true&characterEncoding=utf8&&useSSL=true";
String username="root";
String pwd="123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库 代表数据库
Connection connection = DriverManager.getConnection(url, username, pwd);
//3.向数据库发送SQL的对象Statement, PreparedStatement :CRUD
Statement statement = connection.createStatement();
//4.编写sql
String sql=" SELECT * FROM userinfo";
//5.执行查询sql 返回一个 resultSet
ResultSet rs = statement.executeQuery(sql);
while (rs.next()){
System.out.println("id="+rs.getObject("id"));
System.out.println("name="+rs.getObject("name"));
System.out.println("age="+rs.getObject("age"));
System.out.println("address="+rs.getObject("address"));
}
//6.关闭连接,释放资源 先开后关
rs.close();
statement.close();
connection.close();
}
}
JDBC 预编译
package com.srx;
import java.sql.*;
public class TestJDBC01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
String url="jdbc:mysql://localhost:3306/lx?useUnicode=true&characterEncoding=utf8&&useSSL=true";
String username="root";
String pwd="123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库 代表数据库
Connection connection = DriverManager.getConnection(url, username, pwd);
String sql="insert into userinfo(id,name,age,address)values (?,?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1,4);
ps.setString(2,"wwl");
ps.setInt(3,18);
ps.setString(4,"上海");
int i=ps.executeUpdate();
if (i>0){
System.out.println("ok");
}
else {
System.out.println("no");
}
ps.close();
connection.close();
}
}
JDBC 事务
要么都成功,要么都失败!
AFID原则:保证数据的安全
开启事务
事务提交 commit();
事务回滚 rollback();
关闭事务
Junit 单元测试
代替main方法
@Test 注解只有在方法上有效,只要加了这个注解的方法,就可以直接运行
package com.srx;
public class Test {
@org.junit.Test
public void aa(){
System.out.println("hellow");
System.out.println("123131");
}
}
依赖
<!--单元测试-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
JDBC 事务
package com.srx;
import java.sql.Connection;
import java.sql.DriverManager;
public class Test {
@org.junit.Test
public void aa() {
//配置信息
String url="jdbc:mysql://localhost:3306/lx?useUnicode=true&characterEncoding=utf8&&useSSL=true";
String username="root";
String pwd="123456";
Connection connection=null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库 代表数据库
connection = DriverManager.getConnection(url, username, pwd);
//3.通知数据库开启事务 false 开启
connection.setAutoCommit(false);
String sql="update account set money=money-100 where name='A'";
connection.prepareStatement(sql).executeUpdate();
String sql1="update account set money=money+100 where name='B'";
connection.prepareStatement(sql1).executeUpdate();
connection.commit();
System.out.println("success");
}
catch (Exception e){
try {
connection.rollback();
}
catch (Exception e1){
e1.printStackTrace();
}
}
}
}