JDBC

JDBC

 

DAO:数据库访问对象

DTO:数据传输对象

数据库ß---àDAOß---àDTOß---àpage页面

 

对于表进行增加数据的操作步骤：

//1.通过反射加载数据库驱动

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

//2.获得数据库连接，需要三个信息:统一资源定位器：找到要操作的数据库、用户名、密码

Connection conn =DriverManager.getConnection("jdbc:sqlserver://192.168.43.12:1433;database=userdb","sa","qxh");

//3.操作数据库,通过connection获得加载器，加载sql语句

Statement st =conn.createStatement();

//4.要加载的sql语句

String sql = "insert into student(name,age,sclass,college)values('hebe',22,'java1208','湖大')";

//5.执行sql语句，并返回结果集.

boolean flag = st.execute(sql);

//返回false，表示执行没有错

System.out.println(flag);

//6.关闭连接

conn.close();

 

 

通过Connection获得（预处理）命令执行对象（Statement,PreparedStatement,CallableStatement）

1.  Statement命令可以直接执行完整的sql命令行；

Statement st = conn.createStatement();

St.excute(sql);

2.PreparedStatement支持参数化的sql命令；

    PreparedStatementps = conn.prepareStatement(sql);

    Ps.setString(1,””);

    Ps.excute();

3.CallableStatement支持sql存储过程

String callPCStr= “{call getbookname(?,?)}”;

CallableStatementcs = conn.prepareCall(callPCStr);

Cs.setString(1,””);

//为输出参数注册返回类型

Cs.registerOutParamenter(2,java.sql.Types.VARCHAR);

Cs.excute();

 

二．工厂模型

package com.softeem.factory;

import java.sql.Connection;

import com.softeem.dao.UserDAO;

/**

 * 此工厂是主工厂，负责产生针对不同数据库产品的子工厂

 *@author Administrator

 *

 */

public abstract classDAOFactory

{

       //Sqlserver数据库

       publicstatic final int sql = 1;

      

       //Oracle数据库

       publicstatic final int oracle = 2;

      

       //针对不同的数据类型返回所需要的DAO工厂

       publicstatic DAOFactory getDAOFactory(int db)

       {

              DAOFactoryfactory = null;

              switch(db)

              {

              casesql:

                     factory= new SQLDAOFactory();

                     break;

              caseoracle:

                     factory= new OracleDAOFactory();

                     break;

              default:

                     break;

              }

              returnfactory;

       }

      

       //避免在不同类型的DAO工厂中产生的DAO不一致，所以在主工厂中

       //也对产生的DAO的方式进行统一制定

       publicabstract UserDAO getUserDAO(Connection conn);

      

}

 

package com.softeem.factory;

import java.sql.Connection;

import com.softeem.dao.UserDAO;

public class SQLDAOFactoryextends DAOFactory

{

       @Override

       publicUserDAO getUserDAO(Connection conn)

       {

              //TODO Auto-generated method stub

              UserDAOdao = new UserDAO(conn);

              returndao;

       }

}

 

package com.softeem.factory;

import java.sql.Connection;

import com.softeem.dao.UserDAO;

public classOracleDAOFactory extends DAOFactory

{

       @Override

       publicUserDAO getUserDAO(Connection conn)

       {

              //TODO Auto-generated method stub

              UserDAOdao = new UserDAO(conn);

              returndao;

       }

}

 

 

package com.softeem.factory;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

 

public class ConnectionFactory

{

       //四个常量：驱动，用户名，密码，url。常量的参数必须为大写

       privatestatic final String DRIVER ="com.microsoft.sqlserver.jdbc.SQLServerDriver";

       privatestatic final String USERNAME = "sa";

       privatestatic final String PASSWORD = "qxh";

       privatestatic final String URL = "jdbc:sqlserver://localhost:1433;databasename =userdb";

       //针对ORACLE数据库

       privatestatic final String ORACLEDRIVER ="com.microsoft.sqlserver.jdbc.SQLServerDriver";

       privatestatic final String ORACLEUSERNAME = "sa";

       privatestatic final String ORACLEPASSWORD = "qxh";

       privatestatic final String ORACLEURL ="jdbc:sqlserver://localhost:1433;databasename = userdb";

      

       privatestatic Connection conn;

       publicstatic final int SQL = 1;

       publicstatic final int ORACLE = 2;

       privateint db;

      

       publicConnectionFactory(int db)

       {

              this.db= db;

       }

      

       /**

        * 根据选择的不同的数据库产品来返回连接

        * @param db

        * @return

        * @throws ClassNotFoundException

        * @throws SQLException

        */

       publicstatic Connection getConnection(int db) throws ClassNotFoundException,SQLException

       {

              switch(db)

              {

              caseSQL:

                     Class.forName(DRIVER);

                     conn= DriverManager.getConnection(URL,USERNAME,PASSWORD);

                     break;

              caseORACLE:

                     Class.forName(ORACLEDRIVER);

                     conn= DriverManager.getConnection(ORACLEURL,ORACLEUSERNAME,ORACLEPASSWORD);

                     break;

              default:

                     break;

              }

              returnconn;

       }

}

 

package com.softeem.dao.idao;

import com.softeem.dto.User;

publicinterfaceIUserDAO

{

    publicvoid add(User user);

}

 

 

 

package com.softeem.dao;

 

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.sql.rowset.CachedRowSet;

import com.softeem.dao.idao.IUserDAO;

import com.softeem.dto.User;

import com.sun.rowset.CachedRowSetImpl;

 

public class UserDAOimplements IUserDAO

{

    Connectionconn = null;

   

    publicUserDAO(Connection conn)

    {

       this.conn= conn;

    }

 

    /**

     * 增加用户

     */

    @Override

    publicvoid add(User user)

    {

       //TODO Auto-generated method stub

       Statementstmt = null;

       try

       {

           //创建命令执行对象

           stmt= conn.createStatement();

           Stringsql = "insert into user_tbvalues('"+user.getUsername()+"')";

           //开始执行

           int num = stmt.executeUpdate(sql);

           System.out.println("影响的行数：" + num);

       }catch (Exception e)

       {

           //TODO: handle exception

           e.printStackTrace();

       }finally

       {

           try

           {

              //关闭数据库相关资源

              stmt.close();

              conn.close();

           }catch (SQLException e)

           {

              //TODO Auto-generated catch block

              e.printStackTrace();

           }

       }

//     System.out.println("增加用户");

    }

 

    publicvoid getRs()

    {

       Statementstmt = null;

       ResultSetrs = null;

       try

       {

           //创建命令执行对象

           stmt= conn.createStatement();

           Stringsql = "select * from user_tb";

           //开始执行

//         intnum = stmt.executeUpdate(sql);

//         System.out.println("影响的行数：" + num);

          

           rs= stmt.executeQuery(sql);

           //提前关闭结果集,会导致错误。因为没有存储到数据

           rs.close();

           while(rs.next())

           {

              System.out.println(rs.getString(1));

           }

       }catch (Exception e)

       {

           //TODO: handle exception

           e.printStackTrace();

       }finally

       {

           try

           {

              //关闭数据库相关资源

              stmt.close();

              conn.close();

           }catch (SQLException e)

           {

              //TODO Auto-generated catch block

              e.printStackTrace();

           }

       }

      

    }

   

    publicCachedRowSet getUserRes()

    {

       Statementstmt = null;

       ResultSetrs = null;

       CachedRowSetcs = null;

       try

       {

           //将结果集的游标设置成可以上下滚动的类型，目的主要是让结果集可以指定从某一行开始进行读取

           //ResultSet.CONCUR_READ_ONLY在并发情况下，当前结果集只负责读取数据库中信息，不做其他操作

           stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

           Stringsql = "select * from user_tb";

           rs= stmt.executeQuery(sql);

          

           cs= new CachedRowSetImpl();

           //内存中最多缓存10条数据

           cs.setMaxRows(10);

           //每页显示5条数据

           cs.setPageSize(5);

           //将结果集对象存放到缓存对象中，从数据库中查询到的结果即被放到了缓存结果集中，缓存结果集对象可以在结果集对象关闭的情况下使用

//         cs.populate(rs);

          

           //从结果集中的第二条数据开始取数据放到缓存中

           cs.populate(rs,2);

          

          

           rs.getMetaData();

          

       }catch(Exceptione)

       {

           e.printStackTrace();

           try

           {

              rs.close();

              stmt.close();

              conn.close();

           }catch (Exception e2)

           {

              //TODO: handle exception

              e2.printStackTrace();

           }

       }

       returncs;

    }

}

 

 

 

package com.softeem.dao;

 

import java.lang.reflect.Method;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.List;

import com.softeem.dto.User;

import com.softeem.factory.ConnectionFactory;

 

/**

 * 动态指定所需要的列有哪些，利用反射在dto中存值，list存放dto，add添加dto

查询出所有的列所对应的值，与dto中的属性列对应，利用反射，set值到dto中

最后结果list中放的dto

 *@author Administrator

 *

 */

public class ResultList

{

       publicList<User> refOperValue()

       {

              Connectionconn = null;

              Statementstmt = null;

              ResultSetMetaDatarsm = null;

              ResultSetrs = null;

              List<User>userList = null;

              try

              {

                     conn= ConnectionFactory.getConnection(1);

                     stmt= conn.createStatement();

//                   stmt.execute("select* from user_tb");

//                   rs= stmt.getResultSet();

                     rs= stmt.executeQuery("select * from user_tb");

                     rsm= rs.getMetaData();

                     //获得查询表的列大小

                     intcount = rsm.getColumnCount();

                     userList= new ArrayList<User>();

                     //生成一个字段名数组

                     List<String>columnList = new ArrayList<String>();

                     for(inti=1; i<=count; i++)

                     {

                            columnList.add(rsm.getColumnName(i));

                     }

                     //边循环结果集边判断类型，边进行赋值操作

                     while(rs.next())

                     {

                            Useru = new User();

                            for(inti=1; i<=columnList.size(); i++)

                            {

                                   //取值

                                   Objectvalue = rs.getObject(i);

                                   System.out.println("值类型是：" +value.getClass().getSimpleName());

                                   //值取到后取方法

                                   //StringsetMethod = columnList.get(i);

                                   StringsetMethod = "set" + columnList.get(i-1).substring(0,1).toUpperCase()+

                                                 columnList.get(i-1).substring(1,columnList.get(i-1).length());

                                   //System.out.println("set名是：" + setMethod);

                                  

                                   //考虑封装

                                   ClassuserCls = u.getClass();

                                   Methodmethod = userCls.getMethod(setMethod,value.getClass());

                                   method.invoke(u,value);

                            }

                            userList.add(u);

                     }

              }catch (Exception e)

              {

                     //TODO: handle exception

                     e.printStackTrace();

              }finally

              {

                     try

                     {

                            stmt.close();

                            conn.close();

                     }catch (SQLException e)

                     {

                            //TODO Auto-generated catch block

                            e.printStackTrace();

                     }

              }

             

              returnuserList;

             

       }

      

       publicstatic void main(String[] args)

       {

              ResultListrl = new ResultList();

              List<User>list = rl.refOperValue();

              System.out.println(list.size());

              for(User object : list)

              {

                     System.out.println(object.getUsername());

              }

       }

}