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 class DAOFactory
{
//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());
}
}
}
1万+
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
