DBUtils 增删改查例子
jopen
13年前
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(); } } } </div>