Java JDBC基础（一）

1.准备工作

1.1 MySQL建立表与初始化数据

create table es_t_shop_affiche(

     afficheid int primary key,

     affichetitle varchar(200)

)ENGINE=INNODB default charset utf8;

 

insert into es_t_shop_affiche values(10001,'admin');

insert into es_t_shop_affiche values(10001,'root');

 

1.2 编写创建与关闭数据库连接的工具类

说明：该类只为演示功能,真正投入生产系统需借助连接池,后续会给出连接池最基本的实现

并且此处需要引入MySQL的驱动,本例使用mysql-connector-java-5.1.23-bin.jar

package com.yli.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 创建与关闭JDBC连接
 * @author yli
 *
 */
public class ConnectionUtil {

	/**
	 * 创建连接
	 * @return
	 */
    public static Connection getConnection() {
        String url = "jdbc:mysql://localhost:3306/world";
        String user = "root";
        String password = "123456";
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    /**
     * 关闭连接
     * @param conn
     */
    public static void close(Connection conn){
        if(null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 

 

1.3 为方便查询结果集ResultSet编写工具类RowMapper

 

package com.yli.dal.util;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 简单的结果集与Java对象转换
 * @author yli
 *
 * @param <T>
 */
public class RowMapper<T> {

	/**
	 * 遍历结果集，将数据转换成指定的class类型<br>
	 * 适合结果集包含多条记录
	 * @param classes
	 * @param rs
	 * @return
	 */
    public static <T> List<T> getForList(Class<T> classes, ResultSet rs) {
        List<T> resultList = new ArrayList<T>();
        Field[] fileds = classes.getDeclaredFields();
        try {
            T object;
            while (rs.next()) {
                object = getObject(classes, rs, fileds);
                if (null != object) {
                    resultList.add(object);
                }
            }
            return resultList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

	/**
	 * 遍历结果集，将数据转换成指定的class类型<br>
	 * 适合结果集包含单条记录,如果不是则取第一条
	 * @param classes
	 * @param rs
	 * @return
	 */
    public static <T> T getForObject(Class<T> classes, ResultSet rs) {
        List<T> resultList = getForList(classes, rs);
        if (null != resultList && !resultList.isEmpty()) {
            return resultList.get(0);
        }
        return null;
    }

	/**
	 * 遍历结果集，将数据转换成指定的List<Map<String, Object>>类型<br>
	 * 适合结果集包含单条记录
	 * @param rs
	 */
    public static List<Map<String, Object>> getForList(ResultSet rs) {
        List<Map<String, Object>> resultList = null;
        try {
            resultList = new ArrayList<Map<String, Object>>();
            ResultSetMetaData rsMeta = rs.getMetaData();
            int columnCount = rsMeta.getColumnCount();
            int index;
            String columnLabel;
            while (rs.next()) {
                Map<String, Object> resultMap = new HashMap<String, Object>();
                for (index = 1; index <= columnCount; index++) {
                    columnLabel = rsMeta.getColumnLabel(index);
                    resultMap.put(columnLabel, rs.getObject(columnLabel));
                }
                resultList.add(resultMap);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultList;
    }

	/**
	 * 遍历结果集，将数据转换成指定的Map<String, Object>类型<br>
	 * 适合结果集包含单条记录,如果不是则取第一条
	 * @param rs
	 */
    public static Map<String, Object> getForMap(ResultSet rs) {
        List<Map<String, Object>> resultList = getForList(rs);
        if (null != resultList && !resultList.isEmpty()) {
            return resultList.get(0);
        }
        return null;
    }

    private static <T> T getObject(Class<T> classes, ResultSet rs, Field[] fileds) {
        T object = null;
        try {
            object = classes.newInstance();
            for (Field field : fileds) {
                String fieldName = field.getName();
                field.setAccessible(true);
                field.set(object, rs.getObject(fieldName));
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return object;
    }
}

 

1.4 为方便本例演示,编写与ES_T_SHOP_AFFICHE表结构字段对应的实体类

 

package com.yli.entity;

/**
 * 简单实体,与数据表ES_T_SHOP_AFFICHE字段一一对应<br>
 * 方便将结果集ResultSet映射到该实体上
 * @author yli
 *
 */
public class ShopAffiche {
    private long afficheid;
    private String affichetitle;

    public long getAfficheid() {
        return afficheid;
    }

    public void setAfficheid(long afficheid) {
        this.afficheid = afficheid;
    }

    public String getAffichetitle() {
        return affichetitle;
    }

    public void setAffichetitle(String affichetitle) {
        this.affichetitle = affichetitle;
    }

    @Override
    public String toString() {
        return "[afficheid=" + afficheid + ";affichetitle=" + affichetitle + "]";
    }

}