JDBC操作数据库Mysql

1.JDBC的使用

JDBC 就是使用java语言操作关系型数据库的一套API.

1.创建工程,导入jar包.

MySQL :: Download MySQL Connector/J (Archived Versions)

2.注册驱动

Class.forName("com.mysql.jdbc.Driver");

3.获取连接

String url = "jdbc:mysql://localhost:3306/db2";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);

4.定义sql语句

String sql = " UPDATE account set money = 9999 WHERE id = 1; ";

5.获取执行sql对象

Statement stat = conn.createStatement();

6.执行sql,返回值是受影响的代码行数

int count = stat.executeUpdate(sql);

7.处理返回结果

System.out.println(count);

8.释放资源

stat.close();
conn.close();

整体代码:

package com.yrh;

/*
 * JDBC入门
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class jdbc {
    public static void main(String[] args) throws  Exception{
        // 1. 注册驱动(可省略)
        //Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取连接
        String url = "jdbc:mysql://localhost:3306/db2";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3. 定义sql语句
        String sql = " UPDATE account set money = 9999 WHERE id = 1; ";
        // 4. 获取执行sql的对象 Statement
        Statement stat = conn.createStatement();
        // 5. 执行sql
        int count = stat.executeUpdate(sql);
        // 6. 处理结果
        System.out.println(count);
        // 7. 释放资源
        stat.close();
        conn.close();
    }
}

2.JDBC中的事务处理

package com.yrh;

/*
 * Connection
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class jdbc_2 {
    public static void main(String[] args) throws  Exception{
        // 1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取连接  useSSL=false 禁用安全连接
        String url = "jdbc:mysql://localhost:3306/db2?useSSL=false";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);

        // 3. 定义sql语句
        String sql1 = " UPDATE account set money = 9999 WHERE id = 1; ";
        String sql2 = " UPDATE account set money = 9999 WHERE id = 2; ";
        // 4. 获取执行sql的对象 Statement
        Statement stat = conn.createStatement();

        try {
            // 开启事务
            conn.setAutoCommit(false);
            // 5. 执行sql
            int count1 = stat.executeUpdate(sql1);
            int count2 = stat.executeUpdate(sql2);
            // 6. 处理结果
            System.out.println(count1);
            System.out.println(count2);
            // 提交事务
            conn.commit();
        } catch (Exception e) {
            // 回滚事务
            conn.rollback();
            e.printStackTrace();
        }

        // 7. 释放资源
        stat.close();
        conn.close();
    }
}

3.讲数据库中的数据传入Java对象

package com.yrh;

/*
 * JDBC statement
 */
import com.pojo.Account;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class jdbc_statement {
    public static void main(String[] args) throws  Exception{
        // 1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取连接  useSSL=false 禁用安全连接
        String url = "jdbc:mysql://localhost:3306/db2?useSSL=false";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);
        // 3. 定义sql语句
        String sql = " select * from account";
        // 4. 获取执行sql的对象 Statement
        Statement stat = conn.createStatement();
        // 5. 执行sql
        ResultSet rs = stat.executeQuery(sql);
        List<Account> list = new ArrayList<Account>();
        while(rs.next()){
            Account account = new Account();
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double money = rs.getDouble("money");
            //给对象赋值
            account.setId(id);
            account.setName(name);
            account.setMoney(money);
            list.add(account);
        }
        System.out.println(list);
        // 6. 处理结果
        rs.close();
        // 7. 释放资源
        stat.close();
        conn.close();
    }
}

4.使用PrepareStatement防止sql注入的问题实现模拟登录

package com.yrh;
/*
 * JDBC Preparestatement 放置sql注入
 */
import com.pojo.Account;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class jdbc_prepareStatement {
    public static void main(String[] args) throws  Exception{
        // 1. 注册驱动
        //Class.forName("com.mysql.jdbc.Driver");
        // 2. 获取连接  useSSL=false 禁用安全连接
        String url = "jdbc:mysql://localhost:3306/db2?useSSL=false";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);

        String name = "王伟";
        String pwd = "qfewq";
        String sql = " select * from tb_user where username = ? and password = ?";
        // 4. 获取执行sql的对象 Statement
        PreparedStatement pstmt = conn.prepareStatement(sql);
        // 设置?的值
        pstmt.setString(1,name);
        pstmt.setString(2,pwd);

        ResultSet rs = pstmt.executeQuery();

        if(rs.next()){
            System.out.println("登录成功");
        }else{
            System.out.println("登录失败");
        }
        // 6. 处理结果
        rs.close();
        // 7. 释放资源
        pstmt.close();
        conn.close();
    }
}

5.数据库连接池

数据库连接池是一个容器, 负责分配管理数据库连接;

允许应用程序重复使用一个现有的数据库连接,而不是再重新建立一个;

数据库连接池标准接口:DataSource

获取连接:Connection getConnection()

Druid(德鲁伊):Druid连接池是阿里巴巴开源的数据库连接池项目.

配置文件信息:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db2?useSSL=false&useServerPreStmts=true
username=root
password=1234
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

连接池代码示例:

package com.yrh.jdbc;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.util.Properties;

public class DruidDemo {
    public static void main(String[] args) throws Exception {
        // 1. 导入jar包
        // 2. 定义配置文件
        // 3. 加载配置代码
        Properties prop = new Properties();
        prop.load(new FileInputStream("JDBC/src/druid.properties"));
        // 4. 获取连接对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        // 5. 获取数据库连接
        Connection conn = dataSource.getConnection();
        System.out.println(conn);
        //System.out.println(System.getProperty("user.dir"));
    }
}