暑期实训第二天

mysql jdbc的准备工作

导入jar包

• 下载驱动所需要的jar包，mysql-connector-java-版本号.jar
• 在项目中新建一个lib用于存放该jar包
• 将该包导入项目中(右键add to library)

建立数据库

• 数据库采用的mysql，版本号用的5.4，
• 记住自己的账号和密码，创建一个库为user,新建一个表为userinfo,其中包含3个字段
  

建立实验所用的User类

其放在文件夹bean中,类User包含3个属性和其对应的getter\setter方法及toString()方法。

package Bean;

/**
 * ClassName: User <br/>
 * Description: <br/>
 * date: 2019/7/11 14:24<br/>
 *
 * @author a8243<br />
 * @since JDK 1.8
 */
public class User {
    private int id;
    private String username;
    private String password;

    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;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

编写工具类DBUtil

其放在util文件中

• 其中包含连接数据库的方法，返回connection
• 关闭资源的方法

package util;

import java.sql.*;

/**
 * ClassName: DBUtil <br/>
 * Description: <br/>
 * date: 2019/7/11 14:13<br/>
 *
 * @author a8243<br   />
 * @since JDK 1.8
 */
public class DBUtil {

    public static Connection getConn() {
        Connection connection = null;
        try {
//            1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
//            2.创建连接
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0" +
                    ".1:3306/user?useSSL=true&characterEncoding=utf-8&user" +
                    "=zbz&password=646509whutzbz");
            return connection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void close(ResultSet rs, Statement statement, Connection connection) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

编写UserInfo类

放在bao层中，持久层，包含对数据库的增删改查
这里我简单的实现了增删改查功能

package bao;

import Bean.User;
import util.DBUtil;

import javax.rmi.CORBA.Util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName: UserInfo <br/>
 * Description: <br/>
 * date: 2019/7/11 14:56<br/>
 *
 * @author a8243<br />
 * @since JDK 1.8
 */
public class UserInfo {


    /**
     * @Description:
     * @Param:  * @param
     * @return: java.util.List<Bean.User>
     * @Author: Mr.Zhu
     * @Date: 2019/7/11
     */
    public List<User> findAll(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();
        try {
            connection= DBUtil.getConn();
//            3.sql
            String sql = "select * from userinfo";
//            4.得到statement对象
            statement = connection.prepareStatement(sql);
//            5.执行sql得到结果集
            rs = statement.executeQuery();
//            6.處理結果集
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));;
                user.setPassword(rs.getString(3));
                System.out.println(user);
                list.add(user);
            }
            return list;
//                关闭资源
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, statement, connection);
        }
        return list;
    }




    /**
     * @Description: 添加数据
     * @Param:  * @param username
     * @param password
     * @return: void
     * @Author: Mr.Zhu
     * @Date: 2019/7/11
     */
    public void add(String username, String password){
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            connection = DBUtil.getConn();
            String sql = "insert into userinfo (username,password) values(?,?)";
            statement = connection.prepareStatement(sql);
            statement.setString(1,username);
            statement.setString(2,password);
            statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,statement,connection );
        }
    }
    /**
     * @Description: 删除数据
     * @Param:  * @param username
     * @return: void
     * @Author: Mr.Zhu
     * @Date: 2019/7/11
     */
    public void delete(String uname){
        PreparedStatement statement = null;
        Connection connection = null;
        try {
            connection = DBUtil.getConn();
            String sql = "delete from userinfo where username = ?";
            statement = connection.prepareStatement(sql);
            statement.setString(1,uname);
            statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null,statement,connection);
        }

    }

    public void update(String name, String password){
        PreparedStatement statement = null;
        Connection connection = null;
        try{
            connection = DBUtil.getConn();
            String sql = "update userinfo set password = ? where username = ? ";
            statement = connection.prepareStatement(sql);
            statement.setString(1,password);
            statement.setString(2,name);
            statement.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

编写测试类Test

测试后发现自己写的增删改查功能还有很大的缺陷，需在之后的学习中进行补足。

package com.company;

import Bean.User;
import bao.UserInfo;
import sun.net.sdp.SdpSupport;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName: Test <br/>
 * Description: <br/>
 * date: 2019/7/11 9:19<br/>
 *
 * @author a8243<br   />
 * @since JDK 1.8
 */
public class Test {
    public static void main(String[] args) {

        UserInfo userinfo = new UserInfo();

        //查询数据
        userinfo.findAll();
        //添加数据后，查询数据
        userinfo.add("朱本章","123456");
        userinfo.findAll();
        //删除数据后，查询数据
        userinfo.delete("王小傲");
        userinfo.findAll();
        //更新数据后，查询数据
        userinfo.update("牟俊","654321");
    }
}