宁波暑期实习Day2

Day2 JDBC Bean Dao 学习使用 项目搭建

前提：jdk 版本1.8， mysql 版本 5.7， idea版本 2018.2

1. JDBC编写

1. 新建普通项目
2. 在项目中新建lib文件夹，并导入支持JDBC的jar包，并设置为Library
   
3. 新建数据库和数据表
   
   
4. 在idea中新建Test类，进行代码编写，实现JDBC。
5. 建立数据库连接

 Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user?useSSL=true&characterEncoding=utf-8&user=root");
            System.out.println("connection success");
        } catch (Exception e) {
            e.printStackTrace();
        }

6. 编辑sql语句实现增删查改，在util包中封装连接数据库的操作和断开连接的操作
7. 查询所有数据

public List<UserInfo> findAll () {
        List<UserInfo> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql1 = "select * from userinfo";

            // statement object
            statement = connection.prepareStatement(sql1);
            
            // execute sql statement
            rs = statement.executeQuery();

            // tackle result
            while (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getInt(1));
                userInfo.setUsername(rs.getString(2));
                userInfo.setPassword(rs.getString(3));
                list.add(userInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, connection, statement);
        }
        return list;

8.增删改

 connection = DBUtil.getConnection();
        try {
            statement = connection.prepareStatement(sql);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, connection, statement);
        }

2.Bean类

Bean类就是实体类，这里需要userInfo这个实体类。在实体类中要实现私有变量的getter和setter方法，并且要覆盖toString检查打印结果。

package com.jeleren.bean;

public class UserInfo {
    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 "UserInfo{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

3.Dao类

持久层，与数据库进行交互，增删查改

package com.jeleren.Dao;

import com.jeleren.bean.UserInfo;
import com.jeleren.util.DBUtil;

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

public class UserInfoDao {
    private Connection connection = null;
    private PreparedStatement statement = null;
    private ResultSet rs = null;

    public List<UserInfo> findAll () {
        List<UserInfo> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql1 = "select * from userinfo";

            // statement object
            statement = connection.prepareStatement(sql1);

            // execute sql statement
            rs = statement.executeQuery();

            // tackle result
            while (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getInt(1));
                userInfo.setUsername(rs.getString(2));
                userInfo.setPassword(rs.getString(3));
                list.add(userInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, connection, statement);
        }
        return list;
    }
    public void adu (String sql) {
        connection = DBUtil.getConnection();
        try {
            statement = connection.prepareStatement(sql);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, connection, statement);
        }
    }
  }