JDBC（一）

来源：互联网发布：卖房用什么软件编辑：程序博客网时间：2024/06/05 10:45

. 创建简单t_user测试表

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

建立数据库数据操作(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();                }            }        }      }}

测试代码

    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("");        }    }

0 0