JDBC（一）

1. . 创建简单t_user测试表

create table t_user(
       f_id int,
       f_name varchar2(30),
       f_age int,
       f_job varchar2(30)
);

1. 建立数据库数据操作(DML)的工具类

public class MJdbcTemplate {
    /**
     * 执行数据库的查询操作
     * @param sql
     * @param params
     */
    public List<Map<String,Object>> queryForList(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs=null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String url = "jdbc:oracle:thin:@localhost:1521:orcl";
            String user = "scott";
            String password = "pan";
            conn = DriverManager.getConnection(url, user, password);
            pst = conn.prepareStatement(sql);
            if(params!=null){
                for (int i = 0; i < params.length; i++) {
                    pst.setObject(i + 1, params[i]);
                }
            }

            rs = pst.executeQuery();
            //得到元数据对象
            ResultSetMetaData metaData = rs.getMetaData();
            //得到查询语句列的数量
            int columncount=metaData.getColumnCount();
            //返回结果集
            List<Map<String,Object>> result=new ArrayList<Map<String,Object>>();
            while (rs.next()){
                Map<String,Object> map=new HashMap<String,Object>();
                for(int i=1;i<=columncount;i++){
                    String label=metaData.getColumnLabel(i);
                    Object object=rs.getObject(label);
                    map.put(label, object);
                }
                result.add(map);
            }
            return result;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            if(rs !=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
    /**
     * 执行数据的更新(insert|update|delete)操作
     * @param sql
     * @param params
     * @return 返回受影响的行数
     */
    public int executeUpdate(String sql, Object ... params) {
        Connection conn = null;
        PreparedStatement stm = null;
        try {
            //1.加载驱动类
            Class.forName("oracle.jdbc.OracleDriver");
            //2.建立连接
            String url = "jdbc:oracle:thin:@localhost:1521:orcl";
            String user = "scott";
            String password = "scott";
            conn = DriverManager.getConnection(url, user, password);
            //3.创建SQL语句操作对象(句柄)
            stm = conn.prepareStatement(sql);
            //4.通过SQL语句操作对象执行操作
            //循环替换参数
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    stm.setObject(i + 1, params[i]);
                }
            }
            //executeUpdate:执行数据的INSERT, UPDATE, or DELETE 
            return stm.executeUpdate();
        } catch (Exception e) {
             e.printStackTrace();
             throw new RuntimeException(e);
        } finally {
            //5.释放资源(关闭连接)
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }  
    }

}

1. 测试代码

    public static void main(String[] args){
        MJdbcTemplate template=new MJdbcTemplate();
//      String sql="insert into t_user values (?,?,?,?)";
//      int count=template.executeUpdate(sql, 12,"adminn12",31,"java12");
//      System.out.println(count);

        String sql="select f_id,f_age from t_user where f_id = ?";
        List<Map<String,Object>> queryForList = template.queryForList(sql, 11);
        for (Map<String, Object> map : queryForList) {
            Set<String> keySet = map.keySet();
            for (String key : keySet) {
                System.out.print(key+":"+map.get(key)+"  ");
            }
            System.out.println("");
        }

    }