首页 > 代码库 > 07_数据库创建,添加c3p0操作所需的jar包,编写c3p0-config.xml文件,编写User.java,编写jdbcUtils.java实现操作数据库的模板工具类,UserDao编写,Dao

07_数据库创建,添加c3p0操作所需的jar包,编写c3p0-config.xml文件,编写User.java,编写jdbcUtils.java实现操作数据库的模板工具类,UserDao编写,Dao



1 创建day14数据库,创建user.sql表:

A创建数据库 day14

B创建数据表users

createtable users

(

   id intprimarykeyauto_increment,

   username varchar(20),

   passwordvarchar(20)

);

2 添加依赖的jar

c3p0-0.9.1.2.jar

mysql-connection-java-5.0.8-bin.jar

commons-beanutils-1.8.0.jar

commons-logging.jar

3 编写c3p0-config.xml

<?xmlversion="1.0"encoding="utf-8"?>

<c3p0-config>

   <default-config>

      <propertyname="initialPoolSize">5</property>

      <propertyname="maxPoolSize">20</property>

      <propertyname="driverClass">com.mysql.jdbc.Driver</property>

      <propertyname="jdbcUrl">jdbc:mysql://localhost:3306/day14</property>

      <propertyname="user">root</property>

      <propertyname="password">123456</property>

   </default-config>

   <named-configname="toto">

      <propertyname="initialPoolSize">5</property>

      <propertyname="maxPoolSize">20</property>

      <propertyname="driverClass">com.mysql.jdbc.Driver</property>

      <propertyname="jdbcUrl">jdbc:mysql://localhost:3306/day14</property>

      <propertyname="user">root</property>

      <propertyname="password">123456</property>

   </named-config>

</c3p0-config>

4编写User.java

package cn.toto.domain;

 

publicclass User {

   privateintid;

   private Stringusername;

   private Stringpassword;

   publicint getId() {

      returnid;

   }

   publicvoid setId(int id) {

      this.id = id;

   }

   public String getUsername() {

      returnusername;

   }

   publicvoid setUsername(String username) {

      this.username = username;

   }

   publicString getPassword() {

      returnpassword;

   }

   publicvoid setPassword(String password) {

      this.password = password;

   }

   public User() {

      super();

   }

}

5编写jdbcUtils.java

package cn.toto.utils;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

 

import javax.sql.DataSource;

 

import cn.toto.exception.DaoException;

 

import com.mchange.v2.c3p0.ComboPooledDataSource;

 

public class JdbcUtils {

   private static DataSource ds;

   

   static {

      //ds = new ComboPooledDataSource();//默认的缺省的配置

      ds = new ComboPooledDataSource("toto");//配置文件中设置的内容

   }

   

   //获取与指定数据的连接

   public static DataSource getSource(){

      return ds;

   }

   

   //获得与指定数据库的连接

   public static Connection getConnection() throws SQLException {

      //从连接池返回一个连接

      return ds.getConnection();

   }

   //释放资源

   public static void release(ResultSet rs,Statement stmt,Connection conn) {

      if(rs!=null){

          try{

             rs.close();

          }catch(SQLException e){

             e.printStackTrace();

          }

          rs = null;

      }

      if(stmt!=null){

          try{

             stmt.close();

          }catch(SQLException e){

             e.printStackTrace();

          }

          stmt=null;

      }

      if(conn!=null){

          try{

             conn.close();

          }catch(SQLException e){

             e.printStackTrace();

          }

          conn = null;

      }

   }

   

   //写一个通过的增删改方法

   public static boolean update(String sql,Object[] params) {

      Connection conn = null;

      PreparedStatement pstmt = null;

      ResultSet rs = null;

      

      try {

          //获得连接

          conn = getConnection();

          //SQL

          //预编译sql

          pstmt = conn.prepareStatement(sql);

          //替换参数

          int pos = 1;//设置一个脚标

          //for(Object param : params)   这样写是有错误的,会有空指针异常,要写成如下的方法:

          for(int i=0;params!=null&&i<params.length;i++)

             pstmt.setObject(i+1, params[1]);//由于不知道是什么类型的,故可以用Object

 

          //发送sql

          int num = pstmt.executeUpdate();

          

          //返回结果

          if(num>0)

             return true;

          return false;

      } catch (SQLException e) {

          throw new DaoException(e);

      }finally{

          release(rs,pstmt,conn);

      }

   }

   //实现一个通用的查询方法

   public static Object query(String sql,Object[] params,ResultSetHandler handler){

      Connection conn = null;

      PreparedStatement pstmt = null;

      ResultSet rs = null;

      try{

          conn = getConnection();

          //sql

          //预编译sql

          pstmt = conn.prepareStatement(sql);

          //替换参数

          for(int i=0;params!=null&&i<params.length;i++)

             pstmt.setObject(i+1, params[1]);//由于不知道是什么类型的,故可以用Object

 

          //发送sql

          rs = pstmt.executeQuery();

          //处理结果集

          

          /*我们在方法中需要一种功能让调用者里调用

           * 在方法的内部都有一个模块不会写,有变化,需要留给调用者来处理*/

          

          //这时我们需要用一种设计模式策略模式 TreeSet

          //TreeSet  实现排序,只是实现了部分功能(二叉树,还有没实现的)

          //(元素的比较)让我们传入比较器来实现

          //让我们传入比较器实现Comparatorcompare方法

          /*元素的自然顺序 实现Comparable接口的compareTo方法*/

          

          //向调用者要一个结果集处理器

          Object result = handler.handle(rs);//调用结果处理器的handle方法,它返回的结果就是我们想要的。将结果集变成了一个对象

          return result;

      }catch(SQLException e){

          throw new DaoException(e);

      }finally {

          release(rs,pstmt,conn);

      }

   }

}  

   //写一个通过的增删改方法

   public static boolean update(String sql,Object[] params) {

      Connection conn = null;

      PreparedStatement pstmt = null;

      ResultSet rs = null;

      

      try {

          //获得连接

          conn = getConnection();

          //SQL

          //预编译sql

          pstmt = conn.prepareStatement(sql);

          //替换参数

          int pos = 1;//设置一个脚标

          for(Object param : params)

             pstmt.setObject(pos, param);//由于不知道是什么类型的,故可以用Object

          

          //发送sql

          int num = pstmt.executeUpdate();

          

          //返回结果

          if(num>0)

             return true;

          return false;

      } catch (SQLException e) {

          throw new DaoException(e);

      }finally{

          release(rs,pstmt,conn);

      }

   }

}

6编写UserDao.java

package cn.toto.dao;

 

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.util.ArrayList;

import java.util.List;

 

import cn.toto.domain.User;

importcn.toto.exception.DaoException;

import cn.toto.utils.BeanHandler;

import cn.toto.utils.BeanListHandler;

import cn.toto.utils.JdbcUtils;

importcn.toto.utils.ResultSetHandler;

 

publicclass UserDao {

 

   public List<User> getAll() {

      //sql

      String sql = "select * from users";

      //封装参数

      

      //实现结果集处理器也可以用默认的实现类

      List list = (List) JdbcUtils.query(sql, null,new BeanListHandler(User.class));

      

 

      returnlist;

   }

   

   public User find(int id){

      String sql = "select * from users where id = ?";

      //调用方法

      User user = (User) JdbcUtils.query(sql, new Object[]{id}, new BeanHandler(User.class));

      

      return user;

   }

   

   publicbooleaninsert(User user) {

      /*//Sql

      String sql = "insert into users(username,password) values(?,?)";

      //封装参数

      Object[] params = new Object[2];

      params[0] = user.getUsername();

      params[1] = user.getPassword();

      //调用新方法

      boolean b = JdbcUtils.update(sql, params);

      return b;*/

      

      String sql = "insert into users(username,password) values(?,?)";

      boolean b = JdbcUtils.update(sql,new Object[]{user.getUsername(),user.getPassword()});

      return b;

   }

   

   publicboolean update(User user) {

      String sql = "update users set username=?,password=? where id=?";

      

      return JdbcUtils.update(sql,new Object[]{

             user.getUsername()

             ,user.getPassword()

             ,user.getId()

      });

   }

   

   publicboolean delete(int id) {

      String sql = "delect from users where id=?";

      

      return JdbcUtils.update(sql,new Object[]{id});

   }

}

7编写自定义异常:DaoException.java

package cn.toto.exception;

 

publicclass DaoExceptionextends RuntimeException {

 

   privatestaticfinallongserialVersionUID = 1L;

 

   public DaoException() {

   

   }

 

   public DaoException(String message) {

      super(message);

   }

 

   public DaoException(Throwable cause) {

      super(cause);

   

   }

 

   public DaoException(String message, Throwable cause) {

      super(message, cause);

   

   }

}

8编写BeanHandler.java

在测试类中所有的类中都用到的数据可以写在@BeforeClass中。

package cn.toto.utils;

 

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

importjava.sql.SQLException;

 

import org.apache.commons.beanutils.BeanUtils;

 

importcn.toto.domain.User;

import cn.toto.exception.DaoException;

 

publicclassBeanHandler implements ResultSetHandler {

   privateClass clazz;

   public BeanHandler(Class clazz) {

      this.clazz = clazz;

   }

   

   //将结果集的第一行数据封装到bean返回,要想返回,得加一个返回的类型,上面的代码就是获得类型的代码

   public Object handle(ResultSet rs) {

      try {

          if(rs.next()){

             Object bean = this.clazz.newInstance();//根据传入的创建一个相应类型的bean

             //获得元数据

             ResultSetMetaData metaData = http://www.mamicode.com/rs.getMetaData();

             int columnCount = metaData.getColumnCount();

             for(int i=1;i<=columnCount;i++) {

                 //根据列的编号获得列名

                 String name = metaData.getColumnName(i);

                 //根据列名获得这一行中这一列的值

                 Object value = http://www.mamicode.com/rs.getObject(name);

                 //使用beanutils实现属性的封装

                 BeanUtils.setProperty(bean, name, value);

             }

             //返回bean

             return bean;

          }

          returnnull;

      } catch (Exception e) {

          thrownew DaoException(e);

      }

   }

}

9编写BeanListHandler.java

package cn.toto.utils;

 

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

importjava.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

 

import org.apache.commons.beanutils.BeanUtils;

 

import cn.toto.exception.DaoException;

 

//它的功能是将数据封装到bean,然后再将数据封装到List集合中

publicclass BeanListHandlerimplements ResultSetHandler {

   privateClass clazz;

   public BeanListHandler(Class clazz){

      this.clazz = clazz;

   }

   

   //实现接口,要实现接口中的方法中。

   //将结果集的每一行封装到bean,bean加入一个List返回

   public Object handle(ResultSet rs) {

      

      try{

          List list = newArrayList();

          while(rs.next()){

             Object bean = this.clazz.newInstance();

             //知道列名 把列名作为属性来用

             

             //获得结果集的元数据

             ResultSetMetaData metaData = http://www.mamicode.com/rs.getMetaData();

             //获得列的数量

             int columnCount = metaData.getColumnCount();

             for(int i=1;i<=columnCount;i++){

                 //获得类名

                 String columnName = metaData.getColumnName(i);

                 //获得列对应的值   注意现在结果集的游标是指向某一行的

                 Object value = http://www.mamicode.com/rs.getObject(columnName);

                 BeanUtils.setProperty(bean, columnName, value);

             }

             //bean存入List集合

             list.add(bean);

          }

          return list;

      }catch(Exception e){

          thrownew DaoException(e);

      }

      

   }

}

10编写ResultSetHandler.java

package cn.toto.utils;

 

import java.sql.ResultSet;

 

publicinterface ResultSetHandler {

 

   Object handle(ResultSet rs);

}

12编写UserDaoTest.java

package junit.test;

 

import java.util.List;

 

import org.junit.Test;

 

import cn.toto.dao.UserDao;

import cn.toto.domain.User;

 

publicclass UserDaoTest {

 

   private UserDaodao =new UserDao();

   @Test

   publicvoid testInsert() {

      User user = new User();

       

      user.setUsername("zhangsan");

      user.setPassword("11111111");

      

      boolean b =dao.insert(user);

      System.out.println(b);

   }

   @Test

   publicvoid testGetAll() {

      List all = dao.getAll();

      System.out.println(all);

   }

   @Test

   publicvoid testFind() {

      User user = dao.find(0);

      System.out.println(user);

   }

}

 

07_数据库创建,添加c3p0操作所需的jar包,编写c3p0-config.xml文件,编写User.java,编写jdbcUtils.java实现操作数据库的模板工具类,UserDao编写,Dao