JDBC:Java DataBase Connectivity
Application-统一驱动JDBC-MySQL驱动(mysql.Driver)-MySQL
需要jar包的支持:java.sql、javax.sql、mysql-connector-jar-*.jar连接驱动
建表和插入数据
CREATE TABLE stu(
id INT PRIMARY KEY,
`name` VARCHAR(40),
`password` VARCHAR(40),
email VARCHAR(60),
birthday DATE`student`
);
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(1,'张三','123456','[email protected]','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(2,'李四','123456','[email protected]','2000-01-01');
导入数据库依赖
<!-- mysqld的驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
固定步骤
- 配置信息,加载驱动
- 连接数据库
- 向数据库发送SQL的对象Statement:CRUD
- 编写SQL(根据业务)
- 执行SQL
- 关闭连接
statement,不安全,存在sql注入风险
public class TestJdbc {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="root";
String password="123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库,代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//3.向数据库发送SQL的对象Statement:CRUD,statement不安全
Statement statement = connection.createStatement();
//4.编写SQL
String sql="select * from users";
//5.执行查询SQL,ResultSet结果集
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("id="+resultSet.getObject("id"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("password="+resultSet.getObject("password"));
System.out.println("email="+resultSet.getObject("email"));
System.out.println("birthday="+resultSet.getObject("birthday"));
System.out.println("--------------------------");
}
//6.关闭连接,释放资源(一定要关,先开后关)
resultSet.close();
statement.close();
connection.close();
}
}
prepareStatement预编译,安全,防止SQL注入
public class TestJdbc2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//配置信息
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="root";
String password="123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库,代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//3.编写SQL
String sql="INSERT INTO users(id,`name`,`password`,email,birthday) VALUES(?,?,?,?,?);";
//4.预编译,preparedStatement安全
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,4);
preparedStatement.setString(2,"赵六");
preparedStatement.setString(3,"123456");
preparedStatement.setString(4,"[email protected]");
preparedStatement.setDate(5,new Date(new java.util.Date().getTime()));
//5.执行查询SQL
int i = preparedStatement.executeUpdate();
if(i>0){
System.out.println("新增成功。");
}else{
System.out.println("新增失败。");
}
//6.关闭连接,释放资源(一定要关,先开后关)
preparedStatement.close();
connection.close();
}
}
事务
ACID原则是数据库事务正常执行的四个:
- 原子性,一个事务要么全部执行,要么不执行。
- 一致性,事务的运行不改变数据库中数据的一致性。
- 独立性,也称隔离性,两个以上的事务不会出现交错执行的状态。
- 持久性,不会无缘无故的回滚。
步骤:
- 开启事务
- 事务提交commit()
- 事务回滚rollback()
- 关闭事务
转账:A1000,B1000,A900-100-B1100
public class TestJdbc3 {
@Test
public void test() {
//配置信息
String url="jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="root";
String password="123456";
Connection connection=null;
try {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接数据库,代表数据库
connection = DriverManager.getConnection(url, username, password);
//3.通知数据库开启事务
connection.setAutoCommit(false);
String sql1="update account set money=money-100 where name='A'";
connection.prepareStatement(sql1).executeUpdate();
//制造错误
int i=1/0;
String sql2="update account set money=money+100 where name='B'";
connection.prepareStatement(sql2).executeUpdate();
connection.commit();//以上2条sql都执行成功了,就提交事务。
System.out.println("success");
} catch (Exception e) {
try {
//如果出现异常,数据库回滚。
connection.rollback();
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
e.printStackTrace();
}finally {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
标签:JDBC,复习,数据库,SQL,connection,mysql,password,String
From: https://www.cnblogs.com/liweixiao/p/16716281.html