传统的jdbc样板代码太多。现在提供对jdbc简化的轻量级工具或框架也不少,Spring JDBC Template,jodd db,commons-dbutils,...。我只用过这几个,可能还有更多.这里不谈论orm与jdbc的优劣.为什么要简介这个呢?因为jodd足够轻量并且对官方的api提供了更贴近工作的封闭.适度而灵活.本文只谈一些让您快速上手的api简述
jodd.db.DbManager
通过DBManager可以获取到的关键对象:ConnectionProvider,DbSessionProvider,DbTransactionMode.下面会一一谈到
其实常写jdbc的同仁对上面几个词(Connection,Session,Transaction)并不陌生,我们来一起看看JDBC的代码需要的步骤
1.获取Connection
2.创建一个Statement
3.如果是查询可能会到ResultSet
来一起看一看jodd db是怎样简化的
1:获取Connection / ConnectionProvider
通过DbManager.getConnectionProvider() 或者 ConnectionProvider接口的实现类
ConnectionPoolDataSourceConnectionProvider,
CoreConnectionPool,
DataSourceConnectionProvider,
DriverManagerConnectionProvider,
XADataSourceConnectionProvider
相关对象:DbSession
和hibernate中的session意思一样,对事务的设置或调用都是在此对象上进行
创建方式:
方式1:DbManager.getSessionProvider() .getDbSession()
方式2:new DbSession(ConnectionProvider类实例构造)
到此为止,为下一步工作需要的对象都介绍到了.看一看代码吧:
public static DbSession getJNDIInstance(){
return new DbThreadSession(new DataSourceConnectionProvider(config.getConfig("jndi")));
}
public static DbSession getInstance(){
if(config.getConfig("debug").equals("1")) return getJNDIInstance();
String dc=config.getDbConfig().getDriver();//"com.mysql.jdbc.Driver";
String url=config.getDbConfig().getUrl();//"jdbc:mysql://localhost:3306/iqido";
String un=config.getDbConfig().getUsername();//"root";
String up=config.getDbConfig().getPassword();//"root";
return new DbThreadSession(new DriverManagerConnectionProvider(dc,url,un,up));
}
String SQL="SELECT 列 FROM 表名 WHERE 条件";
DbSession session = ConnectionInstance.getInstance();
DbOomQuery q=new DbOomQuery(session,SQL);
rs=ps.executeQuery();
while(rs!=null && rs.next()){
p=new Provide();
p.setDomain(rs.getString("domain"));
p.setHost(rs.getString("host"));
p.setName(rs.getString("name"));
p.setChannels(rs.getInt("channels"));
}
class ProvideMapper implements QueryMapper<Provide>{
@Override
public Provide process(ResultSet rs) throws SQLException {
// TODO Auto-generated method stub
Provide p=new Provide();
p.setPid(rs.getInt("pid"));
p.setDomain(rs.getString("domain"));
p.setName(rs.getString("name"));
p.setHost(rs.getString("host"));
p.setChannels(rs.getInt("channels"));
return p;
}
}
DbOomQuery在此时用下列方法可以简化jdbc查询时的样板代码
<T> T find(QueryMapper<T> queryMapper)
<T> java.util.List<T> list(QueryMapper<T> queryMapper)
<T> java.util.Set<T> listSet(QueryMapper<T> queryMapper)
@Override
public Provide getProvide(int pid) {
// TODO Auto-generated method stub
String SQL="SELECT pid,domain,name,host,channels FROM king_provide WHERE pid=?";
Provide p=null;
DbSession session = ConnectionInstance.getInstance();
DbOomQuery q = new DbOomQuery(session,SQL);
q.forcePreparedStatement();
q.setInteger(1, pid);
p=q.find(new ProvideMapper());
ConnectionInstance.closeInstance(q,session);
return p;
}
类级用:@DbTable,如果类名与表名不一样,可以用value指定表名:
@DbTable(value="king_provide")
public class Provide {}
@DbTable(value="king_provide")
public class Provide {
@DbId
int pid;
/**
* 域名
*/
@DbColumn
String domain;
/**
* 名称
*/
@DbColumn
String name;
/**
* 主机
*/
@DbColumn
String host;
/**
* 频道数量
*/
@DbColumn
int channels;
}
@Override
public Provide getProvide(int pid) {
// TODO Auto-generated method stub
String SQL="SELECT pid,domain,name,host,channels FROM king_provide WHERE pid=?";
Provide p=null;
DbSession session = ConnectionInstance.getInstance();
DbOomQuery q = new DbOomQuery(session,SQL);
q.forcePreparedStatement();
q.setInteger(1, pid);
p=q.find(Provide.class);
ConnectionInstance.closeInstance(q,session);
return p;
}
ps=conn.prepareStatement(SQL,new String[]{"pid"});
//...
rs=ps.getGeneratedKeys();
DbOomQuery q = new DbOomQuery(session,SQL);
q.setGeneratedColumns("pid");
//...
ResultSet rs = q.getGeneratedColumns();
DbOomQuery q = new DbOomQuery(session,SQL);
q.setGeneratedColumns("pid");
//...
long id = q.getGeneratedKey();
@PetiteBean
public class AppService {
@Transaction
public List<Message> findLastMessages(int count) {
DbSqlBuilder dbsql =
sql("select $C{m.*} from $T{Message m} " +
"order by $m.messageId desc limit :count");
DbOomQuery dbquery = query(dbsql);
dbquery.setInteger("count", count);
return dbquery.list(Message.class);
}
//...
}
5.关于批量sql
jdbc:
void addBatch(String sql)throws SQLExceptionJbQuery.public void setBatch(java.lang.String name,java.lang.Object[] array,int startingIndex)
只是生成sql的占位符数量