dbutils.ExampleJDBC.java Source code

Introduction

Here is the source code for dbutils.ExampleJDBC.java

Source

/*
 * Created on 13-5-25
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except
 * in compliance with the License. You may obtain a copy of the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software distributed under the License
 * is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express
 * or implied. See the License for the specific language governing permissions and limitations under
 * the License.
 * 
 * Copyright @2013 the original author or authors.
 */
package dbutils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import model.Student;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.ProxyFactory;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.wrappers.SqlNullCheckedResultSet;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 * DBUtils?
 * <p/>
 * (2)org.apache.commons.dbutils.handlers
 * ArrayHandler ResultSet??
 * ArrayListHandlerResultSet??ListListObject[]
 * BeanHandler ResultSet??
 * BeanListHandler ResultSet??ListList
 * ColumnListHandler ResultSet???ListListObject
 * KeyedHandler ResultSet?key?MapMap?
 * MapHandler ResultSet??Map
 * MapListHandler ResultSet??ListListMap
 * ScalarHandler ResultSet????Object
 * <p/>
 * (3)org.apache.commons.dbutils.wrappers
 * SqlNullCheckedResultSet ?sql???null?
 * StringTrimmedResultSet ResultSet?Trim()
 *
 * @author XiongNeng
 * @version 1.0
 * @since 13-5-25
 */
public class ExampleJDBC {
    private static final Logger log = LoggerFactory.getLogger(ExampleJDBC.class);

    public static void main(String[] args) {
        JdbcUtil.initDataSourcePool();
        ComboPooledDataSource ds = ((ComboPooledDataSource) JdbcUtil.getDataSource());
        ds.setJdbcUrl("jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8");
        ds.setUser("root");
        ds.setPassword("123456");
        getBeanListData();
    }

    /**
     * BeanListHandler ResultSet??ListList
     */
    public static void getBeanListData() {
        Connection conn = getConnection();
        QueryRunner qr = new QueryRunner();
        try {
            ResultSetHandler<Student> rsh = new BeanHandler(Student.class);
            Student usr = qr.query(conn,
                    "SELECT id, name, gender, age, team_id as teamId FROM test_student WHERE id=1", rsh);
            System.out.println(StringUtils.center("findById", 50, '*'));
            System.out.println("id=" + usr.getId() + " name=" + usr.getName() + " gender=" + usr.getGender());

            List<Student> results = (List<Student>) qr.query(conn,
                    "SELECT id, name, gender, age, team_id as teamId FROM test_student LIMIT 10",
                    new BeanListHandler(Student.class));
            System.out.println(StringUtils.center("findAll", 50, '*'));
            for (Student result : results) {
                System.out.println(
                        "id=" + result.getId() + "  name=" + result.getName() + "  gender=" + result.getGender());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    /**
     * MapListHandler ResultSet??ListListMap
     */
    public static void getMapListData() {
        Connection conn = getConnection();
        QueryRunner qr = new QueryRunner();
        try {
            List results = (List) qr.query(conn, "SELECT id, name, gender, age, team_id FROM test_student",
                    new MapListHandler());
            for (Object result : results) {
                Map map = (Map) result;
                System.out.println(
                        "id=" + map.get("id") + " name=" + map.get("name") + " gender=" + map.get("gender"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    /**
     * ?
     */
    public static void insertAndUpdateData() throws SQLException {
        Connection conn = getConnection();
        QueryRunner qr = new QueryRunner();
        try {
            //??insert?
            Object[] insertParams = { "John Doe", "", 12, 3 };
            int inserts = qr.update(conn, "INSERT INTO test_student(name,gender,age,team_id) VALUES (?,?,?,?)",
                    insertParams);
            System.out.println("inserted " + inserts + " data");

            Object[] updateParams = { "John Doe Update", "John Doe" };
            int updates = qr.update(conn, "UPDATE test_student SET name=? WHERE name=?", updateParams);
            System.out.println("updated " + updates + " data");
        } catch (SQLException e) {
            e.printStackTrace();
            conn.rollback();
        } finally {
            DbUtils.close(conn);
        }
    }

    /**
     * Unlike some other classes in DbUtils, this class(SqlNullCheckedResultSet) is NOT thread-safe.
     */
    public static void findUseSqlNullCheckedResultSet() {
        Connection conn = getConnection();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT id, name, gender, age, team_id as teamId FROM test_student");
            SqlNullCheckedResultSet wrapper = new SqlNullCheckedResultSet(rs);
            wrapper.setNullString("N/A"); // Set null string
            rs = ProxyFactory.instance().createResultSet(wrapper);

            while (rs.next()) {
                System.out.println("id=" + rs.getInt("id") + " username=" + rs.getString("name") + " gender="
                        + rs.getString("gender"));
            }
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    /**
     * *?***
     */
    public static Connection getConnection() {
        try {
            return JdbcUtil.getConnection();
        } catch (Exception e) {
            log.error("??", e);
            return null;
        }
    }
}