DBUtils 增删改查例子

jopen 9年前

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>