几年前写的一个JDBC工具，一直没时间整理出来，断断续续整理了一下，具有特性：
数组SQL参数支持。
分页支持（Oracle，SQLServer，MySQL等），可自定义分页逻辑。
数据库连接共享。
批量数据操作。
轻量，仅依赖slf4j。
为什么要有这玩意，行业内已经有了SpringJDBC，CommonsDbUtils等，整体来说实现思路相似，算是重新发明轮子，
但是如果您想尝尝鲜，可以尝试下。 源代码： GitHubhttps://github.com/chyxion/newbie-jdbc GitOSChttps://git.oschina.net/chyxion/newbie-jdbc使用方式：Maven依赖：
<dependency> <groupId>me.chyxion</groupId> <artifactId>newbie-jdbc</artifactId> <version>0.0.1-RELEASE</version> </dependency>使用样例：
// init datasource, here use DruidDataSource as demoDruidDataSource datasource = null;datasource = new DruidDataSource();datasource.setUrl("jdbc:mysql://127.0.0.1/demo");datasource.setUsername("root");datasource.setPassword("password");datasource.init();// create NewbieJdbc objectNewbieJdbc jdbc = new NewbieJdbcSupport(datasource);BasicQuery
// count of usersint count = jdbc.findValue( "select count(1) from users");// find name of user id is 2008110101String name = jdbc.findValue( "select name from users where id = ?", "2008110101");// find names of user id is 101 or 102// 0. array as paramsList<String> names = jdbc.listValue( "select name from users where id in (?)", "101", "102");// 1. collection as paramsnames = jdbc.listValue( "select name from users where id in (?)", Arrays.asList("101", "102"));// 2. map as paramsMap<String, Object> params = new HashMap<String, Object>();params.put("id", Arrays.asList("101", "102"));// or: // params.put("id", new String[] {"101", "102"});names = jdbc.listValue( "select name from users where id in (:id)", params);// find user of id is 101Map<String, Object> mapUser = jdbc.findMap( "select id, name, gender from users where id = ?", "101");// list users of age is 24List<Map<String, Object>> listUsers = jdbc.listMap( "select id, name, gender from users where age = ?", 24);AdvanceQuery
// find id and name as a string arrayString[] idAndName = jdbc.findOne(new Ro<String[]>() { public String[] exec(ResultSet rs) throws SQLException { return new String[] { rs.getString("id"), rs.getString("name")}; } }, "select id, name from users where id = ?", "101");// find names of gender is MString names = jdbc.list(new Ro<String>() { public String exec(ResultSet rs) throws SQLException { return rs.getString("name"); } }, "select name from users where gender = ?", "M");// find name of user id is 101, same as findValueString name = jdbc.query(new Ro<String>() { public String exec(ResultSet rs) throws SQLException { return rs.next() ? rs.getString(1) : null; } }, "select name from users where id = ?", "101");// list users of gender F offset 10 limit 16List<Map<String, Object>> users = jdbc.listMapPage( "select * from users where gender = ?", Arrays.asList(new Order("date_created", Order.DESC)), 10, 16, "F");InsertAndUpdate
// insert oneMap<String, Object> mapUser = new HashMap<String, Object>();mapUser.put("id", "103");mapUser.put("name", "Shaun Chyxion");mapUser.put("gender", "M");mapUser.put("date_created", new Date());jdbc.insert("users", mapUser);// insert batchCollection<Collection<?>> users = Arrays.<Collection<?>>asList( Arrays.<Object>asList("104", "Xuir", "F", new Date()), Arrays.<Object>asList("105", "Sorina Nyco", "F", new Date()), Arrays.<Object>asList("106", "Gemily", "F", new Date()), Arrays.<Object>asList("107", "Luffy", "M", new Date()), Arrays.<Object>asList("108", "Zoro", "M", new Date()), Arrays.<Object>asList("109", "Bruck", "M", new Date()));jdbc.insert("users", Arrays.asList("id", "name", "gender", "date_created"), args, 3);// update gender to F of user 102jdbc.update("update users set gender = ? where id = ?", "F", "102");Reusble Connection And Transaction// find user of id is 101 and books uses same connectionMap<String, Object> mapUserWithBooks = jdbc.execute(new Co<Map<String, Object>>() { @Override protected Map<String, Object> run() throws SQLException { String userId = "101"; Map<String, Object> mapRtn = findMap( "select * from users where id = ?", userId); mapRtn.put("books", listMap("select * from books where user_id = ?", userId)); return mapRtn; }});// execute transactionMap<String, Object> mapUser = jdbc.executeTransaction(new Co<Map<String, Object>>() { @Override protected Map<String, Object> run() throws SQLException { update("delete users where id = ?", "104"); update("update users set age = ? where id = ?", 24, "103"); return findMap("select * from users where id = ?", 106); }});ExecuteSQL//createtableusers
jdbc.execute( "create table users (" + "id varchar(36) not null, " + "name varchar(36) not null, " + "primary key (id))");CustomizeNewbieJDBC
// create table usersCustomResolver customResolver = new CustomResolver() { // set StringBuilder as String public void setParam(PreparedStatement ps, int index, Object param) throws SQLException { if (param instanceof StringBuilder) { ps.setString(index, param.toString()); } else { ps.setObject(index, param); } } // read CLOB as String public Object readValue(ResultSet rs, int index) throws SQLException { Object valueRtn = null; if (Types.CLOB == rs.getMetaData().getColumnType(index)) { valueRtn = rs.getClob(index).toString(); } else { valueRtn = rs.getObject(index); } return valueRtn; } // use MySQLCompatiblePaginationProcessor to paginate public PaginationProcessor getPaginationProcessor( Connection conn) { return new MySQLCompatiblePaginationProcessor(); }};jdbc = new NewbieJdbcSupport(dataSource, customResolver);Contactschyxion@163.com
评论