Java

java基于JDBC操作数据库

勤劳的小蜜蜂 · 7月2日 · 2019年 ·

项目结构

jar下载

请先将JDBC的jar包导入项目路径中

mysql-connector-java-5.1.5-bin 云盘下载

评论后可见此区域内容

如需其他版本,请前往mvn仓库搜索mysql-connector-java下载

关键代码

数据库

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12346 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, 'laoyi', 15);
INSERT INTO `student` VALUES (2, 'hapi', 21);
INSERT INTO `student` VALUES (3, 'yilei', 18);
INSERT INTO `student` VALUES (1234, 'zhansan', 18);

SET FOREIGN_KEY_CHECKS = 1;

Student.java

package com.ye;

/**
 * @BelongsProject: condb
 * @BelongsPackage: com.ye
 * @Author: Ye
 * @CreateTime: 2019-07-01 22:56
 * @Description:
 */
public class Student {
    private String name;
    private int age;
    private int id;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", age=" + age +
                ", id=" + id +
                '}';
    }
}

dbUtil

package com.ye.utils;

import java.sql.*;

/**
 * @BelongsProject: condb
 * @BelongsPackage: com.ye.utils
 * @Author: Ye
 * @CreateTime: 2019-07-02 01:19
 * @Description:
 */
public class dbUtil {
    private static final String driverClass="com.mysql.jdbc.Driver";
    private static final String Url="jdbc:mysql://localhost:3306/stubase?serverTimezone=GMT%2B8";
    /*
        jdbc:mysql 指定jdbc连接的数据库类型,这里指定的是mysql数据库

        localhost 本地主机名,如果要连接的数据库在其他电脑上,需将localhost改为那个电脑的IP

        3306 数据库端口号,mysql默认端口号为3306

        stubase 数据库的名称

        serverTimezone=GMT%2B8 表示将mysql服务器的时区设置为东八区(中国)的时区,%2意为+,即GMT+8

    */

    private static final String user="root";
    private static final String password="root";
    /*
        user 数据库用户名
        password 数据库用户密码
    */

    //  建立与数据库的连接
    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        //  反射方式注册数据库驱动
        Class.forName(driverClass);
        /*
        如果使用新版 mysql 驱动jar包,须使用com.mysql.cj.jdbc.Driver驱动类进行注册

        MySQL的驱动名为:com.mysql.jdbc.Driver

        SQL Server的驱动名为:com.microsoft.jdbc.sqlserver.SQLServerDriver
        */

        Connection connection = DriverManager.getConnection(Url,user,password);

        return connection;
    }

    //  关闭连接
    public static void closeConnection(Connection connection){
        try{
            if(connection != null || !connection.isClosed()){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //  关闭Statement
    public static void closeStatement(Statement statement){
        try{
            if(statement != null){
                statement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //  关闭ResultSet
    public static void closeResultSet(ResultSet resultSet){
        try{
            if(resultSet != null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Main.java

package com.ye;

import com.ye.utils.dbUtil;

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

/**
 * @BelongsProject: condb
 * @BelongsPackage: PACKAGE_NAME
 * @Author: Ye
 * @CreateTime: 2019-07-01 22:46
 * @Description:
 */
public class Main {

    //  Connection对象获得与数据库的连接
    private static Connection connection;
    public static Connection createConnection() {
        try {
            connection = dbUtil.getConnection();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }


    // 创建Statement对象,用于执行sql语句
    private static Statement statement;
    public static Statement createStatement() {

        try {

            statement = connection.createStatement();
            /*
                通过Connection对象获取Statement对象
                Statement对象用来执行sql语句
                Statement接口有三种执行SQL语句的方法
                    executeQuery():执行查询语句时,一般用executeQuery()方法,该方法执行成功后,会返回一个ResultSet结果集对象。
                    executeUpdate():执行增,删,改语句时,使用executeUpdate()方法,因为增,删,改操作不需要返回结果集对象,返回值是被影响的记录个数。
                    execute():可以用来执行任何SQL语句,执行查询语句时,不会返回结果集,而是返回true;执行增,删,改语句时,也不会返回更改的记录数,而是返回false 所以execute()方法一般在不清楚执行的是查询,还是增,删,改语句时才使用,即在动态执行SQL语句的方法中使用execute()方法
            */

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return statement;
    }

    public static List<Student> select() throws SQLException, ClassNotFoundException {

        //  select * from student
        String sql = "select * from student";

        /*
            executeQuery()执行的结果是ResultSet对象,该对象里保存了SQL语句查询的结果。
            程序可以通过操作该ResultSet对象来取出查询结果。ResultSet对象主要提供了如下两类方法。
            1.next(),previous(),first(),last(),beforeFirst(),afterLast(),absolute()等移动记录指针的方法。
            2.getXXX(列名|列号) 该方法既可以使用列索引作为参数,也可以使用列名作为参数。使用列索引作为参数性能更好,使用列名作为参数可读性更好。
                getString(列名|列号) 取字符串型字段的值
                getInt(列名|列号) 取整型字段的值
                getDouble(列名|列号) 取double型字段的值
                getBoolean(列名|列号) 取布尔型字段的值
                getDate(列名|列号) 取日期型字段的值
                例如:resultSet.getInt("id") 或者 resultSet.getInt(1),id字段所在列号为1
        */

        ResultSet resultSet = statement.executeQuery(sql);
        List<Student> list = new ArrayList<Student>();
        while (resultSet.next()) {
            Student student = new Student();
            student.setId(resultSet.getInt("id"));
            student.setName(resultSet.getString("name"));
            student.setAge(resultSet.getInt("age"));
            list.add(student);
        }

        dbUtil.closeResultSet(resultSet);

        return list;
    }

    public static int insert(Student student) throws SQLException, ClassNotFoundException {

        //  insert into student values('12345','zhansan','18')
        String sql = "insert into student values('" + student.getId() + "','" + student.getName() + "','" + student.getAge() + "')";
        System.out.println(sql);
        return statement.executeUpdate(sql);
    }

    public static int update(Student student) throws SQLException, ClassNotFoundException {

        //  update student set name='zhansan', age=18 where id=12345
        String sql = "update student set name='" + student.getName() + "', age=" + student.getAge() + " where id=" + student.getId();
        System.out.println(sql);
        return statement.executeUpdate(sql);
    }

    public static int delete(Student student) throws SQLException, ClassNotFoundException {

        //  delete from student where id = 12345
        String sql = "delete from student where id = " + student.getId();
        System.out.println(sql);
        return statement.executeUpdate(sql);
    }

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        createConnection();
        createStatement();

        //  创建Student对象用于修改数据
        Student student = new Student();
        student.setId(12345);
        student.setName("zhansan");
        student.setAge(18);

        System.out.println("原纪录" + select() + "\n");

        System.out.println("增加影响的行数:" + insert(student));
        System.out.println("增加之后的列表" + select() + "\n");

        System.out.println("更新影响的行数:" + update(student));
        System.out.println("更新之后的列表" + select() + "\n");

        System.out.println("删除影响的行数:" + delete(student));
        System.out.println("删除之后的列表" + select() + "\n");

        dbUtil.closeStatement(statement);
        dbUtil.closeConnection(connection);
    }
}

源码下载

评论后可见此区域内容
0 条回应