[大数据]MySQL-jdbc

环境maven工程

第一步 添加MySQL驱动

pom.xml

<!--    MySQL驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.23</version>
    </dependency>

第二步 编写连接工具

方式一：MySQL普通连接工具类

1、先添加文件于/resources/jdbc.properties

driverClass=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/datasource?serverTimezone=Asia/Shanghai&useServerPrepStmts=true
userName=root
passWord=******

2、注意按自己需求更改：
①driverClass中cj是MySQL8以后的驱动，MySQL8之前驱动是com.mysql.jdbc.Driver
②url中localhost是主机，本地就是localhost，也可以远程MySQL的主机ip;3306是默认的端口；datasource是要连的数据库名称；
③userName是数据库的用户名
④password是对应数据库的密码

3、最后编写工具类
ConnectionUtils.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;

public class ConnectionUtils {
    private final static String CLASSNAME;
    private final static String URL;
    private final static String USER;
    private final static String PASSWORD;

    private static final ResourceBundle rbget;
    private static Connection con;

    static {
        rbget=ResourceBundle.getBundle("jdbc");
        CLASSNAME=rbget.getString("driverClass");
        URL=rbget.getString("url");
        USER=rbget.getString("userName");
        PASSWORD=rbget.getString("passWord");
    }

    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        Class.forName(CLASSNAME);
        con=DriverManager.getConnection(URL,USER,PASSWORD);
        return con;
    }

    public static void close() throws SQLException {
        con.close();
    }
}

②方式二：c3p0连接池

1、导入依赖
pom.xml

<!--      c3p0数据源（数据库连接池）-->
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>

2、先添加文件于/resources/c3p0-config.xml

<?xml version="1.0" encoding="utf-8" ?>

<c3p0-config>
    <!--默认配置-->
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/datasource?serverTimezone=Asia/Shanghai</property>
        <property name="user">root</property>
        <property name="password">******</property>
        <property name="initialPoolSize">10</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">10</property>
        <property name="maxStatements">200</property>
    </default-config>

</c3p0-config>

注意按需更改连接信息

3、编写c3p0数据库连接池工具类
C3p0Utils.java

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3p0Utils {
    static ComboPooledDataSource cpds=new ComboPooledDataSource();
    static Connection con;
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        con=cpds.getConnection();
        return con;
    }
}

第三步 建表

下面以book表为例，有id,bookname,type,ISBN四个字段

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `bookname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `ISBN` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

第四步 编写实体类

因为想简化代码，所以加了lombok依赖
pom.xml

<dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.22</version>
  </dependency>

Book.java

package entity;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Book {
    Integer id;
    String bookName;
    String type;
    String isbn;
}

@Data包含get ,set,tostring方法，@AllArgsConstructor是全参的构造器，@NoArgsConstructor是无参的构造器，

第五步 使用连接工具类操作数据库

BookDao.Java

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

public class BookDao {
    Connection con;
    PreparedStatement pst;
    ResultSet rs;

    /**
     * 增加
     * @param book
     * @return 影响数据条数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int insert(Book book) throws SQLException, ClassNotFoundException {
        con= ConnectionUtils.getConnection();
        String sql="insert into book values(null,?,?,?) " ;
        pst= con.prepareStatement(sql);
        pst.setString(1,book.getBookName());
        pst.setString(2, book.getType());
        pst.setString(3, book.getIsbn());
        return pst.executeUpdate();
    }

    /**
     * 按ID删除
     * @param id
     * @return 影响数据条数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int delete(Integer id) throws SQLException, ClassNotFoundException {
        con= ConnectionUtils.getConnection();
        String sql="delete  from book where id = ?" ;
        pst= con.prepareStatement(sql);
        pst.setInt(1,id);
        return pst.executeUpdate();
    }

    /**
     * 多个ID批量删除
     * @param ids id的数组
     * @return 影响数据条数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int deleteBatch(List<Integer> ids) throws SQLException, ClassNotFoundException {
        con= ConnectionUtils.getConnection();
        String array =ids.stream().map(s->String.valueOf(s))
                .collect(Collectors.joining(","));
        String sql="delete  from book where id in (?)" ;
        final String replace = sql.replace("?", array);
        pst= con.prepareStatement(replace);
        return pst.executeUpdate();
    }

    /**
     * 按id修改
     * @param book
     * @return 影响数据条数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int update(Book book) throws SQLException, ClassNotFoundException {
        con= ConnectionUtils.getConnection();
        String sql="update book set bookname = ?,type = ?,ISBN = ? where id = ?" ;
        pst= con.prepareStatement(sql);
        pst.setString(1, book.getBookName());
        pst.setString(2, book.getType());
        pst.setString(3, book.getIsbn());
        pst.setInt(4,book.getId());
        return pst.executeUpdate();
    }


    /**
     * 按ID查询
     * @param id
     * @return null或者book实例
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public Book findById(int id) throws SQLException, ClassNotFoundException {
        Book book = null;
        con= ConnectionUtils.getConnection();
        String sql="select * from book\n" +
                "where id=?";
        pst= con.prepareStatement(sql);
        pst.setInt(1,id);
        rs=pst.executeQuery();
        if(rs.next()){
            int RSid  = rs.getInt("id");
            String RSbookName  = rs.getString("bookName");
            String RStype  = rs.getString("type");
            String RSISBN  = rs.getString("ISBN");
            book = new Book(RSid,RSbookName,RStype,RSISBN);
        }
        return book;
    }

    /**
     * 查询所有
     * @return book实例数组
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Book> findAllBooks() throws SQLException, ClassNotFoundException {
        List<Book> bookList = new ArrayList<>();
        con= ConnectionUtils.getConnection();
        String sql="select * from book";
        pst= con.prepareStatement(sql);
        rs=pst.executeQuery();
        while (rs.next()){
            int RSid  = rs.getInt("id");
            String RSbookName  = rs.getString("bookName");
            String RStype  = rs.getString("type");
            String RSISBN  = rs.getString("ISBN");
            Book book = new Book(RSid,RSbookName,RStype,RSISBN);
            bookList.add(book);
        }
        return bookList;
    }

    /**
     * 按条件动态查询
     * @param queryBook 条件
     * @return 符合条件的book数组
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Book> findByQuery(Book queryBook) throws SQLException, ClassNotFoundException {
        List<Book> bookList = new ArrayList<>();
        con= ConnectionUtils.getConnection();
        String bookName = queryBook.getBookName();
        String type = queryBook.getType();
        String addSql ="";
        if(bookName!=null||type!=null){
            addSql = addSql+" where ";
            if(bookName!=null){
                addSql = addSql+"bookname like '%"+bookName+"%'";
                if(type !=null){
                    addSql = addSql+" and type like '%"+type+"%'";
                }
            }else {
                if(type !=null){
                    addSql = addSql+"type like '%"+type+"%'";
                }
            }
        }
        String sql="select * from book "+addSql;
        pst= con.prepareStatement(sql);

        rs=pst.executeQuery();
        while (rs.next()){
            int RSid  = rs.getInt("id");
            String RSbookName  = rs.getString("bookName");
            String RStype  = rs.getString("type");
            String RSISBN  = rs.getString("ISBN");
            Book book = new Book(RSid,RSbookName,RStype,RSISBN);
            bookList.add(book);
        }
        return bookList;
    }

    /**
     * 分页查询
     * @param pageNum 当前页
     * @param pageSize 页大小
     * @return 当前页的所以数据
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Book> findPage(Integer pageNum,Integer pageSize) throws SQLException, ClassNotFoundException {
        List<Book> bookList = new ArrayList<>();
        con= ConnectionUtils.getConnection();
        String sql="select * from book limit ?,?";
        pst= con.prepareStatement(sql);
        pst.setInt(1,(pageNum-1)*pageSize);
        pst.setInt(2,pageSize);
        rs=pst.executeQuery();
        while (rs.next()){
            int RSid  = rs.getInt("id");
            String RSbookName  = rs.getString("bookName");
            String RStype  = rs.getString("type");
            String RSISBN  = rs.getString("ISBN");
            Book book = new Book(RSid,RSbookName,RStype,RSISBN);
            bookList.add(book);
        }
        return bookList;
    }

}

注意以上代码均是用的普通连接
即con= ConnectionUtils.getConnection();
要想用c3p0可替换为con = C3p0Utils.getConnection();