jdbc:java数据库连接
目录
1、首先新建个jbdc.properties文件,引用mysql
6、体会查询代码,因为要展现结果,所以要有ResultSet
1、首先新建个jbdc.properties文件,引用mysql
mysql.username=root
mysql.password=123456
mysql.url=jdbc:mysql://127.0.0.1:3306/aa?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false
mysql.dirvername=com.mysql.cj.jdbc.Driver
2、新建Course.java文件
主要是数据库表里有什么类型的数据,就在这里写出来,然后创建构造函、toString、getter和setter
public class Course {
private Integer id;
private String name;
private Integer t_id;
public Course(Integer id, String name, Integer t_id) {
this.id = id;
this.name = name;
this.t_id = t_id;
}
@Override
public String toString() {
return "Course{" +
"id=" + id +
", name='" + name + '\'' +
", t_id=" + t_id +
'}';
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getT_id() {
return t_id;
}
public void setT_id(Integer t_id) {
this.t_id = t_id;
}
}
3、新建一个jdbc测试类
以下是4种连接jdbc的方法,一个比一个精简,但普通的statement会导致sql注入的问题,这里给大家看一下,后面会介绍
PreparedStatement,用PreparedStatement替代Statement,就不会出现sql注入的问题了
这里为了省事全部用了throws Exception,但在实际项目中不可这样做
@Test
public void test1() throws Exception{
//1.数据库连接的4个基本要素:
String url = "jdbc:mysql://127.0.0.1:3306/aa? " +
"useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPub" +
"licKeyRetrieval=true&useSSL=false";
String user = "root";
String password = "123456";
//8.0之后名字改了 com.mysql.cj.jdbc.Driver
//5.7之后名字改了 com.mysql.jdbc.Driver
String driverName = "com.mysql.cj.jdbc.Driver";
//2.实例化Driver
Class clazz = Class.forName(driverName);
Driver driver = (Driver) clazz.newInstance();
//3.注册驱动
DriverManager.registerDriver(driver);
// 4.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
@Test
public void test2() throws Exception{
//1.数据库连接的4个基本要素:
String url = "jdbc:mysql://127.0.0.1:3306/aa? " +
"useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPub" +
"licKeyRetrieval=true&useSSL=false";
String user = "root";
String password = "123456";
String driverName = "com.mysql.cj.jdbc.Driver";
//2.实例化Driver
Class.forName(driverName);
// 4.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
@Test
public void test3() throws Exception{
//1.数据库连接的4个基本要素:
String url = "jdbc:mysql://127.0.0.1:3306/aa? " +
"useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPub" +
"licKeyRetrieval=true&useSSL=false";
String user = "root";
String password = "123456";
String driverName = "com.mysql.cj.jdbc.Driver";
// 4.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
System.out.println(conn);
}
@Test
public void test4() throws Exception{
Properties properties = new Properties();
properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
String url = properties.getProperty("mysql.url");
String username = properties.getProperty("mysql.username");
String password = properties.getProperty("mysql.password");
// 4.获取连接
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println(conn);
}
4、新建一个jdbc工具类
把jdbc的连接和关闭流的方法写进去,这样调用jdbc时就不用每次要用的时候都要写一遍了
public class JdbcUtil {
public static Connection getConnection(){
Connection conn = null;
try{
Properties properties = new Properties();
properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
String url = properties.getProperty("mysql.url");
String username = properties.getProperty("mysql.username");
String password = properties.getProperty("mysql.password");
// 4.获取连接
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return conn;
}
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
5、体会插入代码
@Test
public void testStatemate(){
Connection connection = null;
try{
String sql1 = "insert into course values (6,'生物',6)";
String sql2 = "update course set name = 1 where id > 1";
connection = JdbcUtil.getConnection();
Statement statement = connection.createStatement();
int i = statement.executeUpdate(sql1);
System.out.println(i);
}catch (SQLException e){
e.printStackTrace();
}finally {
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
6、体会查询代码,因为要展现结果,所以要有ResultSet
@Test
public void testStatemate1(){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try{
String sql1 = "select * from course";
connection = JdbcUtil.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql1);
List<Course> courses = new ArrayList<>();
while (resultSet.next()){
int anInt = resultSet.getInt("id");
String name = resultSet.getString("name");
int t_id = resultSet.getInt("t_id");
courses.add(new Course(anInt,name,t_id));
}
System.out.println(courses);
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(connection,statement,resultSet);
}
}
7、之前提到的sql注入问题
放几行代码展示若是使用Statement,就会造成随便谁都能登录任一用户。Statement可以有空子钻。
public static boolean login(String username,String password){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try{
String sql1 = "select id,username,password from user where username = '"
+ username + "'and password='" + password + "'";
connection = JdbcUtil.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql1);
List<Course> courses = new ArrayList<>();
return resultSet.next();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(connection,statement,resultSet);
}
return false;
}
public static void main(String[] args) {
boolean login = login("张三", "123");
boolean login1 = login("张三", "123' or '1' = '1");
System.out.println(login1);
}
8、用PreparedStatement修改数据
@Test
public void testStatemate3(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try{
String sql1 = "update user set username = '王五' where id = 1";
connection = JdbcUtil.getConnection();
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(connection,preparedStatement,resultSet);
}
}
@Test
public void testStatemate4(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try{
String sql1 = "update user set username = ? where id = ?";
connection = JdbcUtil.getConnection();
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.setString(1,"赵六");
preparedStatement.setInt(2,2);
preparedStatement.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(connection,preparedStatement,resultSet);
}
}
9、用PreparedStatement查询数据
@Test
public void testStatemate5(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try{
String sql1 = "select * from user where id > ?";
connection = JdbcUtil.getConnection();
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.setInt(1,0);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("username"));
System.out.println(resultSet.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(connection,preparedStatement,resultSet);
}
}
10、事务处理
数据一旦提交,就不可回滚。
数据什么时候意味着提交?
-
当一个连接对象被创建时,默认情况下是自动提交事务
:每次执行一个
SQL
语句时,如果执
行成功,就会向数据库自动提交,而不能回滚。
-
关闭数据库连接,数据就会自动的提交。
如果多个操作,每个操作使用的是自己单独的连接,
则无法保证事务。即同一个事务的多个操作必须在同一个连接下。
DBUtils
程序中为了让多个
SQL
语句作为一个事务执行:
-
调用
Connection
对象的
setAutoCommit(false);
以取消自动提交事务
-
在所有的
SQL
语句都成功执行后,调用
commit();
方法提交事务
-
在出现异常时,调用
rollback();
方法回滚事务
@Test
public void testStatemate6(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try{
// 获取连接,并关闭自动提交
connection = JdbcUtil.getConnection();
connection.setAutoCommit(false);
// 数据库操作
String sql1 = "update user set username = ? where id = ?";
preparedStatement = connection.prepareStatement(sql1);
preparedStatement.setString(1,"test1");
preparedStatement.setInt(2,1);
int i = preparedStatement.executeUpdate();
System.out.println(i);
String sql2 = "update user set username = ? where id = ?";
preparedStatement = connection.prepareStatement(sql2);
preparedStatement.setString(1,"test2");
preparedStatement.setInt(2,2);
int i2 = preparedStatement.executeUpdate();
System.out.println(i2);
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
// 若有异常就回滚
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
JdbcUtil.closeAll(connection,preparedStatement,resultSet);
}
}
版权声明:本文为wenqi1原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。