• JDBC JdbTemplate&NamedParameterJdbcTemplate(Spring工具类)


    使用该工具类需要从spring开发包中导入spring.jar和commons-logging.jar,这个模板是线程安全的。

     

    JdbcTemplate:

    public class JdbcTemplateTest {

        static JdbcTemplate jdbc = new JdbcTemplate(JdbcUtils.getDataSource());

        /**
         * @param args
         */
        public static void main(String[] args) {
            User user = findUser("zhangsan");
            // System.out.println("user:" + user);

            // System.out.println("users:" + findUsers(3));

            // System.out.println("user count:" + getUserCount());

            // System.out.println("user name:" + getUserName(1));

            System.out.println("data:" + getData(1));
        }

        static int addUser(final User user) {
            jdbc.execute(new ConnectionCallback() {//想在插入的时候取主键,回调

                public Object doInConnection(Connection con) throws SQLException, DataAccessException {
                    String sql = "insert into user(name,birthday, money) values (?,?,?) ";
                    PreparedStatement ps = con.prepareStatement(sql,
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, user.getName());
                    ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
                    ps.setFloat(3, user.getMoney());
                    ps.executeUpdate();

                    ResultSet rs = ps.getGeneratedKeys();
                    if (rs.next())
                        user.setId(rs.getInt(1));
                    return null;
                }
            });
            return 0;
        }

        static Map getData(int id) {
            String sql = "select id as userId, name, money, birthday  from user where id="
                    + id;
            return jdbc.queryForMap(sql);
        }

        static String getUserName(int id) {
            String sql = "select name from user where id=" + id;
            Object name = jdbc.queryForObject(sql, String.class);
            return (String) name;
        }

        static int getUserCount() {
            String sql = "select count(*) from user";
            return jdbc.queryForInt(sql);//直接把结果返回成int型,也可以查询最大值平均值之类的
        }

        static List findUsers(int id) {
            String sql = "select id, name, money, birthday  from user where id<?";
            Object[] args = new Object[] { id };
            int[] argTypes = new int[] { Types.INTEGER };
            List users = jdbc.query(sql, args, argTypes, new BeanPropertyRowMapper(
                    User.class));//查询结果是多条记录
            return users;
        }

        static User findUser(String name) {
            String sql = "select id, name, money, birthday  from user where name=?";
            Object[] args = new Object[] { name };
            Object user = jdbc.queryForObject(sql, args, new BeanPropertyRowMapper(
                    User.class));//queryForObject查询如果查不到数据或者多于一条数据会报错
            return (User) user;
        }

        static User findUser1(String name) {
            String sql = "select id, name, money, birthday  from user where name=?";
            Object[] args = new Object[] { name };
            Object user = jdbc.queryForObject(sql, args, new RowMapper() {

                public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setName(rs.getString("name"));
                    user.setMoney(rs.getFloat("money"));
                    user.setBirthday(rs.getDate("birthday"));
                    return user;
                }
            });
            return (User) user;
        }
    }

     

     

     

    NamedParameterJdbcTemplate :包含JdbcTemplate的功能,特点是可以解析sql语句中的非?参数,而且可以直接用sql去匹配bean中的属性。

    public class NamedJdbcTemplate {
        static NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(
                JdbcUtils.getDataSource());

        /**
         * @param args
         */
        public static void main(String[] args) {
            User user = new User();
            user.setMoney(10);
            user.setId(2);
            System.out.println(findUser1(user));
        }

        static void addUser(User user) {
            String sql = "insert into user(name,birthday, money) values (:name,:birthday,:money) ";
            SqlParameterSource ps = new BeanPropertySqlParameterSource(user); //使用bean来设置sql中的参数
            KeyHolder keyHolder = new GeneratedKeyHolder(); 
            named.update(sql, ps, keyHolder); //获取主键
            int id = keyHolder.getKey().intValue();
            user.setId(id);
           
            Map map = keyHolder.getKeys(); //如果主键是多个,可以用该方法获取成Map映射
        }

        static User findUser(User user) {
            String sql = "select id, name, money, birthday  from user "
                    + "where money > :m and id < :id";
            Map params = new HashMap();
            // params.put("n", user.getName());
            params.put("m", user.getMoney());
            params.put("id", user.getId()); //使用Map来设置sql的参数
            Object u = named.queryForObject(sql, params, new BeanPropertyRowMapper(
                    User.class)); //第三个参数是一个映射器,将获取的数据映射给Bean
            return (User) u;
        }

        static User findUser1(User user) {
            String sql = "select id, name, money, birthday  from user "
                    + "where money > :money and id < :id";
            SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
            Object u = named.queryForObject(sql, ps, new BeanPropertyRowMapper(
                    User.class));
            return (User) u;
        }

    }

     

     

     

     

    SimpleJdbcTemplate:在前两者基础上支持了可变参数和泛型

    public class SimpleJdbcTemplateTest {
        static SimpleJdbcTemplate simple = new SimpleJdbcTemplate(JdbcUtils
                .getDataSource());


        static User find(String name) {
            String sql = "select id, name, money, birthday  from user where name=? and money > ?";
            User user = simple.queryForObject(sql,
                    ParameterizedBeanPropertyRowMapper.newInstance(User.class),
                    name, 100f);
            return user;
           
            //simple.getNamedParameterJdbcOperations().update(sql,paramSource,keyHolder);先获取NamedJdbcTemplate模板,再获取主键
            //simple.getJdbcOperations()获取JdbcTemplate
        }

    }

     

     

     

    上面代码引用的自定义工具类的代码:

    public final class JdbcUtils {
        private static String url = "jdbc:mysql://localhost:3306/jdbc";
        private static String user = "root";
        private static String password = "";
        private static DataSource myDataSource = null;

        private JdbcUtils() {
        }

        static {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                // myDataSource = new MyDataSource2();
                Properties prop = new Properties();
                // prop.setProperty("driverClassName", "com.mysql.jdbc.Driver");
                // prop.setProperty("user", "user");

                InputStream is = JdbcUtils.class.getClassLoader()
                        .getResourceAsStream("dbcpconfig.properties");
                prop.load(is);
                myDataSource = BasicDataSourceFactory.createDataSource(prop);
            } catch (Exception e) {
                throw new ExceptionInInitializerError(e);
            }
        }

        public static DataSource getDataSource() {
            return myDataSource;
        }

        public static Connection getConnection() throws SQLException {
            // return DriverManager.getConnection(url, user, password);
            return myDataSource.getConnection();
        }

        public static void free(ResultSet rs, Statement st, Connection conn) {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (st != null)
                        st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    if (conn != null)
                        try {
                            conn.close();
                            // myDataSource.free(conn);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                }
            }
        }
    }

  • 相关阅读:
    千万级规模高性能、高并发的网络架构经验分享
    CPU高问题排查
    Worker+MQ解惑
    HashMap解惑
    配置时间同步时,遇到同步无法成功的解决方法
    Django基础—— 1.WEB框架介绍
    html5兼容性问题
    jQuery基础——节点操作
    jQuery基础——基本操作
    jQuery基础——选择器
  • 原文地址:https://www.cnblogs.com/flying607/p/3465595.html
Copyright © 2020-2023  润新知