JDBC

数据库返回一条记录可以用一个实体类对象去封装。
数据库返回一条记录可以用一个Map对象去封装。
数据库返回多条记录可以用一个List<javabean实体类>对象去封装。
数据库返回多条记录可以用一个List<map>对象去封装。

package com.softtech.dao.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.junit.Test;

import com.softtech.entity.EasyBuy_User;
import com.softtech.utils.JdbcUtil;

public class EasyBuy_UserDaoImplSample {
    /**
     * Description:添加记录
     * Author:陈杰
     * 原始的步骤
     * @throws Exception
     */
    @Test
    public void insertEasyBuy_User() throws Exception {
        // 1.导入驱动包
        //     1.1将mysql的jar包放在lib文件夹中
        //     1.2将jar包添加到Referenced Libraries
        
        // 2.加载驱动包
        Class.forName("com.mysql.jdbc.Driver");
        
        // 3.建立数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_easy_buy?useUnicode=true&characterEncoding=UTF-8","root","");
        
        // 4.拼装SQL语句
        Statement stmt = conn.createStatement();
        
        // 5.发送SQL语句到数据库
        String sql = "INSERT INTO easybuy_user SET EU_USER_ID='JaysonChen',EU_USER_NAME='陈杰',EU_PASSWORD='123456',EU_SEX='T',EU_STATUS=1";
        int flag = stmt.executeUpdate(sql);
        if(flag>=1) {
            System.out.println(flag+" Insert successfully!!!");
        } else {
            System.out.println("Insert failed!!!");
        }
        
        // 6.释放资源
        stmt.close();
        conn.close();
    }
    
    
    /**
     * Description:添加记录性能测试
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void insertEasyBuy_UserSpeed() throws Exception {
        long startTime = System.currentTimeMillis();
        
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_easy_buy?useUnicode=true&characterEncoding=UTF-8","root","");
        Statement stmt = conn.createStatement();
        for(int i=1;i<=1000;i++) {
            String sql = "INSERT INTO easybuy_user SET EU_USER_ID='JaysonChen"+i+"',EU_USER_NAME='陈杰',EU_PASSWORD='123456',EU_SEX='T',EU_STATUS=1";
            int flag = stmt.executeUpdate(sql);
            if(flag>=1) {
                System.out.println(flag+" Insert successfully!!!");
            } else {
                System.out.println("Insert failed!!!");
            }
        }
        stmt.close();
        conn.close();
        
        long endTime = System.currentTimeMillis();
        System.out.println("Time consuming "+(endTime-startTime)+"ms");
    }
    
    
    /**
     * Description:删除记录
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void deleteEasyBuy_User() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_easy_buy?useUnicode=true&characterEncoding=UTF-8","root","");
        Statement stmt = conn.createStatement();
        String sql = "Delete from easybuy_user where EU_USER_ID like 'JaysonChen%'";
        int flag = stmt.executeUpdate(sql);
        if(flag>0) {
            System.out.println("Deleted successfully "+flag+" Record!!!");
        } else {
            System.out.println("Delete failed!!!");
        }
        stmt.close();
        conn.close();
    }
    
    
    /**
     * Description:修改记录
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void updateEasyBuy_User() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_easy_buy?useUnicode=true&characterEncoding=UTF-8","root","");
        Statement stmt = conn.createStatement();
        String sql = "Update easybuy_user set EU_ADDRESS = '深圳' where EU_USER_ID like 'JaysonChen%'";
        int flag = stmt.executeUpdate(sql);
        if(flag>0) {
            System.out.println("Update successfully "+flag+" Record!!!");
        } else {
            System.out.println("Update failed!!!");
        }
        stmt.close();
        conn.close();
    }
    
    
    /**
     * Description:查询单条记录
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void selectOneEasyBuy_User1() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_easy_buy?useUnicode=true&characterEncoding=UTF-8","root","");
        Statement stmt = conn.createStatement();
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user where EU_USER_ID='admin'";
        ResultSet rest = stmt.executeQuery(sql);
        
        //定义一个Map对象存储一条记录。
        Map<String,Object> ebuMap = new HashMap<String,Object>();
        while(rest.next()) {
            String user = rest.getString("EU_USER_ID");
            Timestamp time = rest.getTimestamp("EU_BIRTHDAY");
            BigDecimal status = rest.getBigDecimal("EU_STATUS");
            
            //存储字段名，值
            ebuMap.put("user", user);
            ebuMap.put("time", time);
            ebuMap.put("status", status);
        }
        rest.close();
        stmt.close();
        conn.close();
        
        //打印出Map的记录
        for(Entry<String,Object> ety:ebuMap.entrySet()) {
            System.out.println(ety.getValue()+"\t");
        }
        
        //lambda表达式
        ebuMap.forEach((k,v)-> {
            System.out.println(k+"="+v);
        });
    }
    
    
    /**
     * Description:查询所有记录
     * Author:陈杰
     * 
     * @throws Exception
     */
    @Test
    public void selectAllEasyBuy_User2() throws Exception {
        //使用工具类获取连接
        Connection conn = JdbcUtil.getConn();
        Statement stmt = conn.createStatement();
        String sql = "SELECT EU_USER_ID,EU_BIRTHDAY,EU_STATUS from easybuy_user order by EU_USER_ID asc";
        ResultSet rest = stmt.executeQuery(sql);
        
        //定义一个List存储多行记录
        List<EasyBuy_User> ebuList = new ArrayList<EasyBuy_User>();
        while(rest.next()) {
            String user = rest.getString("EU_USER_ID");
            Timestamp time = rest.getTimestamp("EU_BIRTHDAY");
            BigDecimal status = rest.getBigDecimal("EU_STATUS");
            
            //定义一个实体类存储一行记录
            EasyBuy_User ebu = new EasyBuy_User();
            ebu.setEu_user_id(user);
            ebu.setEu_birthday(time);
            ebu.setEu_status(status);
            
            //存储
            ebuList.add(ebu);
        }
        //使用工具类关闭连接
        JdbcUtil.close(rest, stmt, conn);
        
        //打印出List的记录
        for(EasyBuy_User ebu:ebuList) {
            System.out.println(ebu.getEu_user_id()+" "+ebu.getEu_birthday()+" "+ebu.getEu_status());
        }
        
    }
    
}