java - mysql 连接数据库

一、JDBC配置步骤

步骤

1.加载驱动
2.创建连接
3.编写sql
4.创建存放sql语句的对象
5.执行sql,并得到返回结果 -- executeQuery:返回对象集和结果集，查询，executeUpdate:返回的是整数，增删改
6.遍历结果集
7.关闭连接

代码

import java.sql.*;

/**
 * @Author jinliwei
 * @Date 2019/9/26 14:11
 **/
public class JDBC {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/exam?useSSL=true&characterEncoding=utf-8","root","123456");
            System.out.println("数据库连接成功"+conn);
            //3.编写sql
            String sql = "select * from user";
            //4.创建存放sql语句的对象
            pstm = conn.prepareStatement(sql);
            //5.执行sql,并得到返回结果 -- executeQuery:返回对象集和结果集，查询，executeUpdate:返回的是整数，增删改
            rs = pstm.executeQuery();
            //6.遍历结果集
            while (rs.next()){
                System.out.println("id为："+rs.getInt(1));
                System.out.println("用户名为："+rs.getString(2));
                System.out.println("用户名密码为："+rs.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //7.关闭连接
            if (rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (pstm != null){
                try {
                    pstm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

二、使用DBUtile工具类连接数据库

DBUtils类

import java.sql.*;

/**
 * @Author jinliwei
 * @Date 2019/9/26 14:40
 **/
public class DBUtil {
    private static String Driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost3306/bookstore?useSSL=true&characterEncoding=utf-8";
    private static String user = "root";
    private static String password = "123456";

    //加载驱动
    static {
        try {
            Class.forName(Driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //连接数据库
    public static Connection get_Conn() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }
    //关闭数据库
    public static void get_CloseConn(ResultSet rs,PreparedStatement pstm,Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pstm != null){
            try {
                pstm.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}

UserDao类

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * @Author jinliwei
 * @Date 2019/9/26 14:49
 **/
public class UserDao {
    Connection conn = null;
    PreparedStatement pstm = null;
    ResultSet rs = null;
    Scanner input = new Scanner(System.in);
    public void addUser(UserInfo userInfo){
        try {
            //1.创建连接数据库
            DBUtil.get_Conn();
            //2.创建存放sql语句的对象PreparedStatement
            pstm = conn.prepareStatement("insert into userinfor(id,userName,password) values(?,?,?)");
            //2.1 填坑
            pstm.setInt(1,userInfo.getId());
            pstm.setString(2,userInfo.getUserName());
            pstm.setString(3,userInfo.getPassword());
            //3.执行sql语句，并得到返回结果
            int i = pstm.executeUpdate();
            if (i>0){
                System.out.println("增加成功");
            } else {
                System.out.println("增加失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.get_CloseConn(null,pstm,conn);
        }
    }

    public static void main(String[] args) {
        UserInfo userInfo = new UserInfo(5,"王二麻子","12314564");
        UserDao userDao = new UserDao();
        userDao.addUser(userInfo);

    }
}

UserInfo实体类

/**
 * @Author jinliwei
 * @Date 2019/9/26 14:50
 **/
public class UserInfo {
    private int id;
    private String userName;
    private String password;

    //get and set 访问器
    //User user = new User();
    //user.get/set
    public int getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    //构造方法
    //User user = new User(传值)；
    public UserInfo() {
    }

    public UserInfo(int id, String userName, String password) {
        this.id = id;
        this.userName = userName;
        this.password = password;
    }

    //toString 方法重写
    @Override
    public String toString() {
        return "UserInfo{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}