JDBC
使用mysql数据库
1. 使用jdbc步骤
- 加载数据库驱动
- 数据库用户信息和连接url
- 创建数据库连接
- 创建数据操作对象
- 执行sql操作
- 关闭数据库连接
package com.suzezhi.jdbc;
import java.sql.*;
public class TestJDBC {
/*
* 测试数据操作
* */
public static void main(String[] args) throws Exception {
//加载数据库驱动
Class driver = Class.forName("com.mysql.jdbc.Driver");
//数据库用户信息和连接url
String url="jdbc:mysql://localhost:3306/jdbc-test?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username="root";//数据库的用户名
String password="123456";//数据库的密码
//创建数据库连接对象,代表数据库
Connection connection = DriverManager.getConnection(url,username,password);
//创建数据库操作对象
Statement statement = connection.createStatement();
//执行相关数据库语句
String sql="select * from stu_jdbc";
ResultSet resultSet = statement.executeQuery(sql);
//拿查询结果的内容
while (resultSet.next()){
System.out.print("id:"+resultSet.getString("id")+"\t");
System.out.print("username:"+resultSet.getString("username")+"\t");
System.out.print("email:"+resultSet.getString("email")+"\t");
System.out.println();
}
//关闭数据库相关的连接
resultSet.close();
statement.close();
connection.close();
}
}
String url="jdbc:mysql://localhost:3306/jdbc-test?useUnicode=true&characterEncoding=utf8&useSSL=false";
后面的useUnicode=true&characterEncoding=utf8&useSSL=false 在高版本的MySQL数据库中需要设置,5.7之前的可以不用设置。
2.statement对象
statement对象用于操作执行sql语句的。
方法名 | 说明 | 例子 |
---|---|---|
ResultSet executeQuery(String sql) | 该方法用于执行sql查询,返回一个查询的结果集 | statement.executeQuery(“select * from table_name”) |
int executeUpdate(String sql) | 该方法用于sql的增删改,返回一个影响的行数 | statement.executeUpdate("delete from table_name where id=‘1’ ") |
void close() | 该方法用于操作结束后关闭statement | statement.close() |
例子:
配置数据库相关信息的文件db.properties
#配置数据库相关信息的文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc-test?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
数据库连接工具类
//数据库连接工具类
package com.suzezhi.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class DBUtils {
private static String driver=null;
private static String url=null;
private static String username=null;
private static String password=null;
static {
try {
//读取db.properties配置文件
InputStream inputStream = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
//读取配置文件的内容
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//加载数据库驱动
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//创建数据库连接对象
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//关闭数据库连接
public static void DBclose(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (resultSet!=null){
resultSet.close();
}if (statement!=null){
statement.close();
}if (connection!=null){
connection.close();
}
}
}
查询数据库内容
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJdbc2 {
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = DBUtils.getConnection();
// 创建statement对象
Statement statement = connection.createStatement();
//查询数据内容
String selectSql="select * from stu_jdbc";
ResultSet resultSet = statement.executeQuery(selectSql);
while (resultSet.next()){
System.out.print("id:"+resultSet.getString("id")+"\t");
System.out.print("username:"+resultSet.getString("username")+"\t");
System.out.print("email:"+resultSet.getString("email")+"\t");
System.out.print("创建时间:"+resultSet.getTimestamp("createTime")+"\t");
System.out.print("最后修改时间:"+resultSet.getTimestamp("lastTime")+"\t");
System.out.println();
}
//关闭连接
DBUtils.DBclose(connection,statement,resultSet);
}
}
插入数据内容
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJdbc2 {
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = DBUtils.getConnection();
// 创建statement对象
Statement statement = connection.createStatement();
// 插入数据
String insertSql="insert into stu_jdbc(username,email) values('小小','xiaoxiao@qq.com')";
int i = statement.executeUpdate(insertSql);
if(i>0){
System.out.println("插入成功");
}
//关闭连接
DBUtils.DBclose(connection,statement,null);
}
}
删除数据
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJdbc2 {
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = DBUtils.getConnection();
// 创建statement对象
Statement statement = connection.createStatement();
// 插入数据
String delSql="delete from stu_jdbc where id='5'";
int i = statement.executeUpdate(delSql);
if(i>0){
System.out.println("删除成功");
}
//关闭连接
DBUtils.DBclose(connection,statement,null);
}
}
更新数据
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJdbc2 {
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = DBUtils.getConnection();
// 创建statement对象
Statement statement = connection.createStatement();
//更新数据
String updeteSql="update stu_jdbc set username='小黄',email='xiaohuang@qq.com' where id='1'";
int i = statement.executeUpdate(updeteSql);
if(i>0){
System.out.println("更新成功");
}
//关闭连接
DBUtils.DBclose(connection,statement,null);
}
}
3.PreparedStatement对象
通常都是使用PreparedStatement来操作SQL,不使用上面的Statement来操作Sql,由于Statement对象进行传递动态参数(就是sql语句需要传递变量作为参数时,使用了字符串拼接)会引起SQL注入问题。
例子:配置文件和工具类与上面的Statement一样
查询
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
// 防止sql注入,使用PreparedStatement
public class TestJDBC3 {
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = DBUtils.getConnection();
//查询
/*
*创建操作对象preparedStatement
* 使用?做占位符
* */
String selectSql="select * from stu_jdbc where id=?";
PreparedStatement preparedStatement = connection.prepareStatement(selectSql);//预编译,不会执行sql
//设置参数,第一个参数是表示第几个参数(就是上面sql的第几个?),后面是传递的值
preparedStatement.setInt(1,1);
//执行sql
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.print("id:"+resultSet.getString("id")+"\t");
System.out.print("username:"+resultSet.getString("username")+"\t");
System.out.print("email:"+resultSet.getString("email")+"\t");
System.out.print("创建时间:"+resultSet.getTimestamp("createTime")+"\t");
System.out.print("最后修改时间:"+resultSet.getTimestamp("lastTime")+"\t");
System.out.println();
}
//关闭连接
DBUtils.DBclose(connection,preparedStatement,resultSet);
}
}
插入
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
// 防止sql注入,使用PreparedStatement
public class TestJDBC3 {
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = DBUtils.getConnection();
//插入
/*
*创建操作对象preparedStatement
* 使用?做占位符
* */
//插入数据
String insertSql="insert into stu_jdbc(username,email) values(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertSql);
//设置参数
preparedStatement.setString(1,"小明");
preparedStatement.setString(2,"xiaoming@qq.com");
//执行sql语句
int i = preparedStatement.executeUpdate();
if(i>0){
System.out.println("插入成功");
}
//关闭连接
DBUtils.DBclose(connection,preparedStatement,null);
}
}
更新数据
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
// 防止sql注入,使用PreparedStatement
public class TestJDBC3 {
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = DBUtils.getConnection();
/*
*创建操作对象preparedStatement
* 使用?做占位符
* */
//更新数据
String updateSql="update stu_jdbc set email=?,lastTime=? where username=?";
PreparedStatement preparedStatement = connection.prepareStatement(updateSql);
//设置参数
preparedStatement.setString(1,"xiaoming@169.com");
preparedStatement.setTimestamp(2,new Timestamp(new Date().getTime()));
preparedStatement.setString(3,"小明");
//执行sql语句
int i = preparedStatement.executeUpdate();
if(i>0){
System.out.println("更新成功");
}
//关闭连接
DBUtils.DBclose(connection,preparedStatement,null);
}
}
删除数据
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
// 防止sql注入,使用PreparedStatement
public class TestJDBC3 {
public static void main(String[] args) throws SQLException {
//连接数据库
Connection connection = DBUtils.getConnection();
/*
*创建操作对象preparedStatement
* 使用?做占位符
* */
//删除数据
String updateSql="delete from stu_jdbc where username=?";
PreparedStatement preparedStatement = connection.prepareStatement(updateSql);
//设置参数
preparedStatement.setString(1,"小明");
//执行sql语句
int i = preparedStatement.executeUpdate();
if(i>0){
System.out.println("删除成功");
}
//关闭连接
DBUtils.DBclose(connection,preparedStatement,null);
}
}
4. 操作事务
事务特点:
- 原子性:要么成功,要么失败
- 一致性:总数不变
- 隔离性:多个进程之间操作互不影响
- 持久性:一旦更改就不可逆转
JDBC操作事务步骤:
- 开启事务
connection.setAutoCommit(false);
,关闭自动提交,默认开启事务- 写事务相关代码
- 成功就提交事务,失败就回滚
package com.suzezhi.jdbc;
import com.suzezhi.utils.DBUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestTransaction {
private static Connection connection=null;
private static PreparedStatement preparedStatement=null;
public static void main(String[] args) throws SQLException {
try {
connection = DBUtils.getConnection();
//开启事务,默认关闭自动提交就开启事务(JDBC)
connection.setAutoCommit(false);
//执行相关事务操作
String sql = "insert into stu_jdbc(username,email) values(?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "天天");
preparedStatement.setString(2, "tian@qq.com");
preparedStatement.executeUpdate();
//更改id=1的内容
String sql1 = "update stu_jdbc set username=? where id=?";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.setString(1, "小何");
preparedStatement.setInt(2, 1);
preparedStatement.executeUpdate();
//提交事务
connection.commit();
}catch (Exception e){
e.printStackTrace();
//失败就回滚事务,默认失败回滚
// try {
// connection.rollback();
// } catch (SQLException throwables) {
// throwables.printStackTrace();
// }
}finally {
DBUtils.DBclose(connection,preparedStatement,null);
}
}
}