sql
CREATE TABLE [dbo].[Person] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[userName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[age] [int] NULL
) ON [PRIMARY]
GO
1：取得一行记录.DataSource
package com.x.test;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.sql.DataSource;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
public class TEST01_getFirstRow
{
public static void main(String[] args) throws SQLException
{
TEST01_getFirstRow t = new TEST01_getFirstRow();
t.test();
}
private void test() throws SQLException
{
// Create a ResultSetHandler implementation to convert the
// first row into an Object[].
ResultSetHandler<Object[]> h = new ResultSetHandler<Object[]>()
{
public Object[] handle(ResultSet rs) throws SQLException
{
if (!rs.next())
{
return null;
}
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
Object[] result = new Object[cols];
for (int i = 0; i < cols; i++)
{
result[i] = rs.getObject(i + 1);
}
return result;
}
};
// Create a QueryRunner that will use connections from
// the given DataSource
JtdsDataSource dataSource = new JtdsDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("DBtest");
dataSource.setUser("sa");
dataSource.setPassword("");
QueryRunner run = new QueryRunner(dataSource);
// Execute the query and get the results back from the handler
Object[] result = run.query("SELECT * FROM Person WHERE username!=?", h, "John Doe");
System.out.println("over");
}
}
2：取得一行记录，Connection
package com.x.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.sql.DataSource;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
public class TEST02_getFirstRow2
{
public static void main(String[] args) throws SQLException
{
TEST02_getFirstRow2 t = new TEST02_getFirstRow2();
t.test();
}
private void test() throws SQLException
{
// Create a ResultSetHandler implementation to convert the
// first row into an Object[].
ResultSetHandler<Object[]> h = new ResultSetHandler<Object[]>()
{
public Object[] handle(ResultSet rs) throws SQLException
{
if (!rs.next())
{
return null;
}
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
Object[] result = new Object[cols];
for (int i = 0; i < cols; i++)
{
result[i] = rs.getObject(i + 1);
}
return result;
}
};
// Create a QueryRunner that will use connections from
// the given DataSource
JtdsDataSource dataSource = new JtdsDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("DBtest");
dataSource.setUser("sa");
dataSource.setPassword("");
Connection conn = DriverManager.getConnection(
"jdbc:jtds:sqlserver://localhost:1433/DBtest;", "sa", "");
QueryRunner run = new QueryRunner();
try
{
Object[] result = run.query(conn,
"SELECT * FROM Person WHERE userName!=?", h, "John Doe");
// do something with the result
System.out.println("over");
}
finally
{
// Use this helper method so we don't have to check for null
DbUtils.close(conn);
}
System.out.println("over");
}
}
3：添加/更新
package com.x.test;
import java.sql.SQLException;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;
import org.apache.commons.dbutils.QueryRunner;
public class TEST03_insertData
{
/**
* @param args
*/
public static void main(String[] args)
{
TEST03_insertData insert=new TEST03_insertData();
insert.test();
}
private void test()
{
JtdsDataSource dataSource=new JtdsDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("DBtest");
dataSource.setUser("sa");
dataSource.setPassword("");
QueryRunner run = new QueryRunner( dataSource );
try
{
// Execute the SQL update statement and return the number of
// inserts that were made
int inserts = run.update( "INSERT INTO Person (userName,age) VALUES (?,?)",
"zhanghongjie", 122 );
// The line before uses varargs and autoboxing to simplify the code
// Now it's time to rise to the occation...
int updates = run.update( "UPDATE Person SET age=? WHERE username=?",
2, "zhanghongjie" );
// So does the line above
System.out.println("over");
}
catch(SQLException sqle) {
// Handle it
}
}
}
4：异步执行更新
package com.x.test;
import java.sql.SQLException;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.concurrent.FutureTask;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;
import org.apache.commons.dbutils.AsyncQueryRunner;
public class TEST04_asyncQueryRunner
{
public static void main(String[] args)
{
TEST04_asyncQueryRunner t = new TEST04_asyncQueryRunner();
t.test();
}
private void test()
{
JtdsDataSource dataSource = new JtdsDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("DBtest");
dataSource.setUser("sa");
dataSource.setPassword("");
// ExecutorCompletionService<Integer> executor = new
// ExecutorCompletionService<Integer>(
// Executors.newCachedThreadPool());
AsyncQueryRunner asyncRun = new AsyncQueryRunner(dataSource, Executors
.newCachedThreadPool());
try
{
for (int i = 0; i < 10; i++)
{
System.out.println("over1");
}
// Create a Callable for the update call
Future futures = asyncRun.update(
"UPDATE Person SET age=? WHERE username=?", 120,
"zhanghongjie");
for (int i = 0; i < 10; i++)
{
System.out.println("over2");
}
System.out.println(futures.get());//如果有必要取得返回结果
for (int i = 0; i < 10; i++)
{
System.out.println("over3");
}
// Submit the Callable to the executor
// executor.submit(callable);
}
catch (Exception sqle)
{
// Handle it
}
// Sometime later (or in another thread)
// try
// {
// // Get the result of the update
// Integer updates = executor.take().get();
// System.out.println("over");
// }
// catch (Exception ie)
// {
// // Handle it
// }
}
}
这个官方的例子错了。
5：动态映射类取得一条记录
注意：Person.java 有三个字段
private String USERNAME;//跟数据库中字段名称不用要求大小写一样
private String age;//数据库类型和java类型没有关系
private int age1;//多出来字段会被赋予基本类型的默认值
package com.x.test;
import java.sql.SQLException;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import com.x.vo.Person;
public class TEST05_getByclz
{
public static void main(String[] args)
{
TEST05_getByclz t = new TEST05_getByclz();
t.test();
}
private void test()
{
JtdsDataSource dataSource = new JtdsDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("DBtest");
dataSource.setUser("sa");
dataSource.setPassword("");
QueryRunner run = new QueryRunner(dataSource);
// Use the BeanHandler implementation to convert the first
// ResultSet row into a Person JavaBean.
ResultSetHandler<Person> h = new BeanHandler<Person>(Person.class);
// Execute the SQL statement with one replacement parameter and
// return the results in a new Person object generated by the
// BeanHandler.
try
{
Person p = run.query("SELECT * FROM Person WHERE id=?", h, "1");
System.out.println(p);
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
6：动态映射类取得List
package com.x.test;
import java.sql.SQLException;
import java.util.List;
import net.sourceforge.jtds.jdbcx.JtdsDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.x.vo.Person;
public class TEST06_getListByclz
{
public static void main(String[] args)
{
TEST06_getListByclz t = new TEST06_getListByclz();
t.test();
}
private void test()
{
JtdsDataSource dataSource = new JtdsDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("DBtest");
dataSource.setUser("sa");
dataSource.setPassword("");
QueryRunner run = new QueryRunner(dataSource);
// Use the BeanHandler implementation to convert the first
// ResultSet row into a Person JavaBean.
ResultSetHandler<List<Person>> h = new BeanListHandler<Person>(Person.class);
// Execute the SQL statement with one replacement parameter and
// return the results in a new Person object generated by the
// BeanHandler.
try
{
List<Person> p = run.query("SELECT * FROM Person ", h);
System.out.println(p);
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
转自：http://www.open-open.com/lib/view/open1331524877984.html