1、jdbc-odbc
/**
*演示使用jdbc-odbc桥连方式操作数据库
*1、配置数据源(数据源：服务器S（.或local,点小三角会死机）)
*2、在程序中去连接数据源
*
*/
import java.sql.*;
public class Demo
{
public static void main(String args[])
{
Connection=null;
Statement=null;
try{
//1、加载驱动(作用是把需要的驱动程序加入内存)
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//该jdbc是sun公司开发的
//2、得到连接(指定连接到哪个数据源，用户名和密码)
//如果你配置数据源的时候，选择是windows nt验证，则不需要"sa","shunping"
//即Connection ct=DriverManager.getConnection("jdbc:odbc:mytext")
Connection ct=DriverManager.getConnection("jdbc:odbc:mytext","sa","123456");
//3、创建Statement或者PreparedStatement[区别]
//Statement 用处是：主要用于发送sql语句,到数据库（如：打电话通道：无线、用线）
Statement sm=ct.createStatement();
//4、执行(crud,增删改查数据库。。。)
/*
//1)演示添加一条数据到dept表,删除、修改和添加相似
//executeUpdate可以执行crud操作
int i=sm.excuteUpdate("insert into dept values('50','保安部','西安')");
if(i==1){
System.out.println("添加ok");
}
else{
System.out.println("添加error");
}
*/
//2)查询
// ResultSet结果集,理解成一个表行的结果集,游标
ResultSet rs=sm.executeQuery("select * from dept");
//因为rs指向结果集第一行的上一行
//不知道有多少行，则可循环取出
while(rs.next())
{
int deptno=rs.getInt(1);
String dname=rs.getString(2);
String loc=rs.getString(3);
System.out.println(deptno+" "+dname+" "+loc);
}
}
catch(Exception e){
e.printStackTrace();
}
finally
{
//关闭资源（非常重要!!!）
//关闭顺序是，谁后创建，则先关闭
try
{//为了使程序健壮
if(sm!=null)//如果等于null表明已经关闭
{
sm.close();
}
if(ct!=null)
{
ct.close();
}
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
}
2、jdbc
/**
*演示使用jdbc方式操作数据库
*1、引入java.sql.*
*2、加载驱动，即jar包
*3、特别说明，如果取值是按编号，则需要一一对应，如果是按照列名取值，则顺序可颠倒
*/
import java.sql.*;
public class Demo
{
public static void main(String args[])
{
//PreparedStatement[火箭车]
//定义需要的对象
Connection ct=null;
PreparedStatement ps=null;
ResultSet rs=null;
try{
//1、加载驱动(作用是把需要的驱动程序加入内存)
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//该jdbc是ms公司开发的
//2、得到连接(不用配置数据源)
ct=DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;
databaseName=itimeIBMS","sa","123456");
//3、创建PreparedStatement火箭车
//执行【如果是增加、删除、修改executeUpdate(),如果是查询则用executeQuery】
ps=ct.PreparedStatement(" select deptno,dname,loc from dept ");//可以用?赋值
rs=ps.executeQuery();
while(rs.next())
{
int deptno=rs.getInt(1);
String dname=rs.getString(2);
String loc=rs.getString(3);//注意标号，也可以把编号用名称代替
System.out.println(deptno+" "+dname+" "+loc);
}
//4、使用PreparedStatement添加一条记录,删除、修改类似
ps=ct.preparedStatement("insert into dept value(?,?,?)");
ps.setInt(1,50);
ps.setString(2,"安全部");
ps.setString(3,"北京");
int i=ps.excuteUpdate();
if(i==1){
System.out.println("添加ok");
}
else{
System.out.println("添加error");
}
}
catch(Exception e){
e.printStackTrace();
}
finally
{
//关闭资源（非常重要!!!）
//关闭顺序是，谁后创建，则先关闭
try
{//为了使程序健壮
if(rs!=null)
{
rs.close();
}
if(ps!=null)//如果等于null表明已经关闭
{
ps.close();
}
if(ct!=null)
{
ct.close();
}
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
}
3、数据库操作
/**
*演示java中使用ddl语句
*1、引入java.sql.*
*2、加载驱动，即jar包
*
*/
import java.sql.*;
public class Demo
{
public static void main(String args[])
{
//PreparedStatement[火箭车]
//定义需要的对象
Connection ct=null;
PreparedStatement ps=null;
try{
//1、加载驱动(作用是把需要的驱动程序加入内存)
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");//该jdbc是ms公司开发的
//2、得到连接(不用配置数据源)，驱动名称
//127.0.0.1表示你要连接的数据库的ip
//1433表示sql server的默认端口（一个计算机有65535个端口）
ct=DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;
databaseName=itimeIBMS","sa","123456");
//3、创建PreparedStatement火箭车
//执行【如果是增加、删除、修改executeUpdate(),如果是查询则用executeQuery】
//如果执行的是ddl语句
ps=ct.PreparedStatement(" create database aaa ");
blooean b=ps.execute();
if(b){
System.out.println("ok");
}
else{
System.out.println("error");
}
}
catch(Exception e){
e.printStackTrace();
}
finally
{
//关闭资源（非常重要!!!）
//关闭顺序是，谁后创建，则先关闭
try
{//为了使程序健壮
if(rs!=null)
{
rs.close();
}
if(ps!=null)//如果等于null表明已经关闭
{
ps.close();
}
}
if(ct!=null)//如果等于null表明已经关闭
{
ct.close();
}
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
}
4、比较PreparedStatement和Statement
现在都是用PreparedStatement
/**
*PreparedStatement
*1、PreparedStatement可以提高执行效率（因为它有预编译的功能）
*2、PreparedStatement可以防止SQL注入，但是要求用?赋值的方式才可以
*/
import java.sql.*;
public class Demo
{
public static void main(String args[])
{
Connection ct=null;
PreparedStatement ps=null;
ResultSet rs=null;
try{
//1、加载驱动(作用是把需要的驱动程序加入内存)
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");//该jdbc是sun公司开发的
//2、得到连接(指定连接到哪个数据源，用户名和密码)
//如果你配置数据源的时候，选择是windows nt验证，则不需要"sa","shunping"
//即Connection ct=DriverManager.getConnection("jdbc:odbc:mytext")
Connection ct=DriverManager.getConnection("jdbc:odbc:mytext","sa","shunping");
//3、创建PreparedStatement
ps=PreparedStatement(" select * from dept where username='shunping' and password='shunping' ");//可以用?赋值
rs=ps.executeQuery();
while(rs.next())
{
int deptno=rs.getInt(1);
String dname=rs.getString(2);
String loc=rs.getString(3);
System.out.println(deptno+" "+dname+" "+loc);
}
//使用PreparedStatement添加一条记录,删除、修改类似
ps=ct.preparedStatement("insert into dept value(?,?,?)");
ps.setInt(1,50);
ps.setString(2,"安全部");
ps.setString(3,"北京");
int i=ps.excuteUpdate();
if(i==1){
System.out.println("添加ok");
}
else{
System.out.println("添加error");
}
}
catch(Exception e){
e.printStackTrace();
}
finally
{
//关闭资源（非常重要!!!）
//关闭顺序是，谁后创建，则先关闭
try
{//为了使程序健壮
if(rs!=null)
{
rs.close();
}
if(ps!=null)//如果等于null表明已经关闭
{
ps.close();
}
if(ct!=null)
{
ct.close();
}
}
catch(SQLException e)
{
e.printStackTrace();
}
}
}
}
}
5.jdbc数据库连接池
文件：
package com.sino.ibms.db; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DBConnection { private Connection conn; public DBConnection(Connection conn) { this.conn = conn; } public CallableStatement getCallableStatement(String spName) throws SQLException { CallableStatement cstmt = this.conn.prepareCall("{call " + spName + "}"); return cstmt; } public PreparedStatement getPreparedStatement(String sql) throws SQLException { PreparedStatement pstmt = this.conn.prepareStatement(sql, 1004, 1007); return pstmt; } public Statement getStatement() throws SQLException { Statement stmt = this.conn.createStatement(1005, 1008); return stmt; } public ResultSet execQuery(String sql) throws SQLException { Statement stmt = getStatement(); ResultSet rs = stmt.executeQuery(sql); return rs; } public ResultSet execQuery(String sql, int startNO, int pagesize) throws SQLException { PreparedStatement preparedStatement = getPreparedStatement(sql); preparedStatement.setMaxRows(startNO + pagesize - 1); ResultSet rs = preparedStatement.executeQuery(); rs.absolute(startNO - 1); return rs; } public void execUpdate(String sql) throws SQLException { PreparedStatement pstmt = getPreparedStatement(sql); pstmt.execute(); } public int execCommand(String sql) throws SQLException { Statement stmt = getStatement(); return stmt.executeUpdate(sql); } }
package com.sino.ibms.db; import java.io.PrintStream; import java.sql.Connection; import java.util.Enumeration; import java.util.Hashtable; import java.util.Iterator; import java.util.Vector; public class DBConnectionManager { private static DBConnectionManager instance; private static int clients; private Vector drivers = new Vector(); private Hashtable pools = new Hashtable(); public DBConnectionManager() { init(); } public static synchronized DBConnectionManager getInstance() { if (instance == null) { instance = new DBConnectionManager(); } return instance; } public void freeConnection(String name, Connection con) { DBConnectionPool pool = (DBConnectionPool)this.pools.get(name); if (pool != null) pool.freeConnection(con); } public Connection getConnection(String name) { DBConnectionPool pool = null; Connection con = null; pool = (DBConnectionPool)this.pools.get(name); con = pool.getConnection(); if (con != null) System.out.println("得到连接....."); return con; } public Connection getConnection(String name, long timeout) { DBConnectionPool pool = null; Connection con = null; pool = (DBConnectionPool)this.pools.get(name); con = pool.getConnection(timeout); System.out.println("得到连接。。。"); return con; } public synchronized void release() { Enumeration allpools = this.pools.elements(); while (allpools.hasMoreElements()) { DBConnectionPool pool = (DBConnectionPool)allpools.nextElement(); if (pool != null) pool.release(); } this.pools.clear(); } private void createPools(DSConfigBean dsb) { DBConnectionPool dbpool = new DBConnectionPool(); dbpool.setName(dsb.getName()); dbpool.setDriver(dsb.getDriver()); dbpool.setUrl(dsb.getUrl()); dbpool.setUser(dsb.getUsername()); dbpool.setPassword(dsb.getPassword()); dbpool.setMaxConn(dsb.getMaxconn()); System.out.println("ioio:" + dsb.getMaxconn()); this.pools.put(dsb.getName(), dbpool); } private void init() { loadDrivers(); Iterator alldriver = this.drivers.iterator(); while (alldriver.hasNext()) { createPools((DSConfigBean)alldriver.next()); System.out.println("创建连接池。。。"); } System.out.println("创建连接池完毕。。。"); } private void loadDrivers() { ParseDSConfig pd = new ParseDSConfig(); this.drivers = pd.readConfigInfo("ds.config.xml"); System.out.println("加载驱动程序。。。"); } public static void main(String[] args) { } }
package com.sino.ibms.db; import java.io.PrintStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.ArrayList; import java.util.Iterator; import java.util.Timer; public class DBConnectionPool { private Connection con = null; private int inUsed = 0; private ArrayList freeConnections = new ArrayList(); private int minConn; private int maxConn; private String name; private String password; private String url; private String driver; private String user; public Timer timer; public DBConnectionPool() { } public DBConnectionPool(String name, String driver, String URL, String user, String password, int maxConn) { this.name = name; this.driver = driver; this.url = URL; this.user = user; this.password = password; this.maxConn = maxConn; } public synchronized void freeConnection(Connection con) { this.freeConnections.add(con); this.inUsed -= 1; } public synchronized Connection getConnection(long timeout) { Connection con = null; if (this.freeConnections.size() > 0) { con = (Connection)this.freeConnections.get(0); if (con == null) con = getConnection(timeout); } else { con = newConnection(); } if ((this.maxConn == 0) || (this.maxConn < this.inUsed)) { con = null; } if (con != null) { this.inUsed += 1; } return con; } public synchronized Connection getConnection() { Connection con = null; if (this.freeConnections.size() > 0) { con = (Connection)this.freeConnections.get(0); this.freeConnections.remove(0); if (con == null) con = getConnection(); System.out.println("继续链接......."); } else { con = newConnection(); System.out.println("建立新连接......."); } if ((this.maxConn == 0) || (this.maxConn < this.inUsed)) { con = null; } if (con != null) { this.inUsed += 1; System.out.println("得到 " + this.name + " 的连接，现有" + this.inUsed + "个连接在使用!"); } return con; } public synchronized void release() { Iterator allConns = this.freeConnections.iterator(); while (allConns.hasNext()) { Connection con = (Connection)allConns.next(); try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } this.freeConnections.clear(); } private Connection newConnection() { try { Class.forName(this.driver); this.con = DriverManager.getConnection(this.url, this.user, this.password); } catch (ClassNotFoundException e) { e.printStackTrace(); System.out.println("sorry can't find db driver!"); } catch (SQLException e1) { e1.printStackTrace(); System.out.println("sorry can't create Connection!"); } return this.con; } public synchronized void TimerEvent() { } public static void main(String[] args) { } public String getDriver() { return this.driver; } public void setDriver(String driver) { this.driver = driver; } public int getMaxConn() { return this.maxConn; } public void setMaxConn(int maxConn) { this.maxConn = maxConn; } public int getMinConn() { return this.minConn; } public void setMinConn(int minConn) { this.minConn = minConn; } public String getName() { return this.name; } public void setName(String name) { this.name = name; } public String getPassword() { return this.password; } public void setPassword(String password) { this.password = password; } public String getUrl() { return this.url; } public void setUrl(String url) { this.url = url; } public String getUser() { return this.user; } public void setUser(String user) { this.user = user; } }
<?xml version="1.0" encoding="UTF-8"?>
<ds-config>
<!--纳米城停车场-->
<pool>
<type>SQLServer2005</type>
<name>namipark</name>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
<url>jdbc:sqlserver://localhost:1433;DatabaseName=ykt</url>
<username>sa</username>
<password>123456</password>
<maxconn>100</maxconn>
</pool>
<!-- 纳米城门禁 -->
<pool>
<type>SQLServer2005</type>
<name>namidoor</name>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
<url>jdbc:sqlserver://localhost:1433;DatabaseName=NewAICDatabase</url>
<username>sa</username>
<password>123456</password>
<maxconn>100</maxconn>
</pool>
<!-- 纳米城巡更 -->
<pool>
<type>SQLServer2005</type>
<name>namixg</name>
<driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
<url>jdbc:sqlserver://localhost:1433;DatabaseName=PATROLXP</url>
<username>sa</username>
<password>123456</password>
<maxconn>100</maxconn>
</pool>
</ds-config>
package com.sino.ibms.db; public class DSConfigBean { private String type = ""; private String name = ""; private String driver = ""; private String url = ""; private String username = ""; private String password = ""; private int maxconn = 0; public static void main(String[] args) { } public String getDriver() { return this.driver; } public void setDriver(String driver) { this.driver = driver; } public int getMaxconn() { return this.maxconn; } public void setMaxconn(int maxconn) { this.maxconn = maxconn; } public String getName() { return this.name; } public void setName(String name) { this.name = name; } public String getPassword() { return this.password; } public void setPassword(String password) { this.password = password; } public String getType() { return this.type; } public void setType(String type) { this.type = type; } public String getUrl() { return this.url; } public void setUrl(String url) { this.url = url; } public String getUsername() { return this.username; } public void setUsername(String username) { this.username = username; } }
package com.sino.ibms.db; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.PrintStream; import java.net.URL; import java.util.Iterator; import java.util.List; import java.util.Vector; import javax.servlet.http.HttpServletRequest; import org.apache.struts2.ServletActionContext; import org.jdom.Document; import org.jdom.Element; import org.jdom.JDOMException; import org.jdom.input.SAXBuilder; import org.jdom.output.Format; import org.jdom.output.XMLOutputter; public class ParseDSConfig { public Vector readConfigInfo(String path) { String rpath = getClass().getResource("").getPath().substring(1) + path; HttpServletRequest request = ServletActionContext.getRequest(); System.out.println("===ClassLoader===" + getClass().getResource("ds.config.xml").getPath()); System.out.println("===Class===" + getClass().getResource("/")); System.out.println("===config===" + rpath); System.out.println("===ContextPath===" + request.getContextPath()); System.out.println("===RealPath===" + request.getRealPath("/")); System.out.println("===ServletPath===" + request.getServletPath()); System.out.println("===RequestURI===" + request.getRequestURI()); Vector dsConfig = null; FileInputStream fi = null; InputStream is = null; try { is = getClass().getResourceAsStream("ds.config.xml"); dsConfig = new Vector(); SAXBuilder sb = new SAXBuilder(); Document doc = sb.build(is); Element root = doc.getRootElement(); List pools = root.getChildren(); Element pool = null; Iterator allPool = pools.iterator(); while (allPool.hasNext()) { pool = (Element)allPool.next(); DSConfigBean dscBean = new DSConfigBean(); dscBean.setType(pool.getChild("type").getText()); dscBean.setName(pool.getChild("name").getText()); System.out.println(dscBean.getName()); dscBean.setDriver(pool.getChild("driver").getText()); dscBean.setUrl(pool.getChild("url").getText()); dscBean.setUsername(pool.getChild("username").getText()); dscBean.setPassword(pool.getChild("password").getText()); dscBean.setMaxconn(Integer.parseInt(pool.getChild("maxconn") .getText())); dsConfig.add(dscBean); } } catch (FileNotFoundException e) { e.printStackTrace(); try { if (fi != null) fi.close(); if (is != null) is.close(); } catch (IOException e1) { e1.printStackTrace(); } } catch (JDOMException e) { e.printStackTrace(); try { if (fi != null) fi.close(); if (is != null) is.close(); } catch (IOException e1) { e1.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); try { if (fi != null) fi.close(); if (is != null) is.close(); } catch (IOException e1) { e1.printStackTrace(); } } finally { try { if (fi != null) fi.close(); if (is != null) is.close(); } catch (IOException e) { e.printStackTrace(); } } return dsConfig; } public void modifyConfigInfo(String path, DSConfigBean dsb) throws Exception { String rpath = getClass().getResource("").getPath().substring(1) + path; FileInputStream fi = null; FileOutputStream fo = null; } public void addConfigInfo(String path, DSConfigBean dsb) { String rpath = getClass().getResource("").getPath().substring(1) + path; FileInputStream fi = null; FileOutputStream fo = null; try { fi = new FileInputStream(rpath); SAXBuilder sb = new SAXBuilder(); Document doc = sb.build(fi); Element root = doc.getRootElement(); List pools = root.getChildren(); Element newpool = new Element("pool"); Element pooltype = new Element("type"); pooltype.setText(dsb.getType()); newpool.addContent(pooltype); Element poolname = new Element("name"); poolname.setText(dsb.getName()); newpool.addContent(poolname); Element pooldriver = new Element("driver"); pooldriver.addContent(dsb.getDriver()); newpool.addContent(pooldriver); Element poolurl = new Element("url"); poolurl.setText(dsb.getUrl()); newpool.addContent(poolurl); Element poolusername = new Element("username"); poolusername.setText(dsb.getUsername()); newpool.addContent(poolusername); Element poolpassword = new Element("password"); poolpassword.setText(dsb.getPassword()); newpool.addContent(poolpassword); Element poolmaxconn = new Element("maxconn"); poolmaxconn.setText(String.valueOf(dsb.getMaxconn())); newpool.addContent(poolmaxconn); pools.add(newpool); Format format = Format.getPrettyFormat(); format.setIndent(""); format.setEncoding("utf-8"); XMLOutputter outp = new XMLOutputter(format); fo = new FileOutputStream(rpath); outp.output(doc, fo); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (JDOMException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } public void delConfigInfo(String path, String name) { String rpath = getClass().getResource("").getPath().substring(1) + path; FileInputStream fi = null; FileOutputStream fo = null; try { fi = new FileInputStream(rpath); SAXBuilder sb = new SAXBuilder(); Document doc = sb.build(fi); Element root = doc.getRootElement(); List pools = root.getChildren(); Element pool = null; Iterator allPool = pools.iterator(); while (allPool.hasNext()) { pool = (Element)allPool.next(); if (pool.getChild("name").getText().equals(name)) { pools.remove(pool); break; } } Format format = Format.getPrettyFormat(); format.setIndent(""); format.setEncoding("utf-8"); XMLOutputter outp = new XMLOutputter(format); fo = new FileOutputStream(rpath); outp.output(doc, fo); } catch (FileNotFoundException e) { e.printStackTrace(); try { fi.close(); } catch (IOException e1) { e1.printStackTrace(); } } catch (JDOMException e) { e.printStackTrace(); try { fi.close(); } catch (IOException e1) { e1.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); try { fi.close(); } catch (IOException e1) { e1.printStackTrace(); } } finally { try { fi.close(); } catch (IOException e) { e.printStackTrace(); } } } public static void main(String[] args) throws Exception { ParseDSConfig pd = new ParseDSConfig(); String path = "ds.config.xml"; pd.readConfigInfo(path); DSConfigBean dsb = new DSConfigBean(); dsb.setType("oracle"); dsb.setName("yyy004"); dsb.setDriver("org.oracle.jdbc"); dsb.setUrl("jdbc:oracle://localhost"); dsb.setUsername("sa"); dsb.setPassword(""); dsb.setMaxconn(1000); pd.addConfigInfo(path, dsb); pd.delConfigInfo(path, "yyy001"); } }
使用：
package com.sino.ibms.action; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import org.apache.commons.collections.map.LinkedMap; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.struts2.ServletActionContext; import com.opensymphony.xwork2.ActionContext; import com.opensymphony.xwork2.ActionSupport; import com.sino.ibms.common.Pager; import com.sino.ibms.db.DBConnection; import com.sino.ibms.db.DBConnectionManager; import com.sino.ibms.model.NamiDoorEvent; public class NamiDoorAction extends ActionSupport { private Map cnameMap; private Map penameMap; private Map eventnameMap; String cname; String pename; String eventname; String btime; String etime; String fileName; private InputStream excelStream; public Pager pager; public InputStream getExcelStream() { return excelStream; } public void setExcelStream(InputStream excelStream) { this.excelStream = excelStream; } public String getFileName() { return fileName; } public void setFileName(String fileName) { this.fileName = fileName; } public Pager getPager() { return pager; } public void setPager(Pager pager) { this.pager = pager; } // 查询方法 public String doorQuery() throws IOException, SQLException { try { HttpServletRequest request = ServletActionContext.getRequest(); DBConnectionManager connectionManager = DBConnectionManager .getInstance(); Connection conn = connectionManager.getConnection("namidoor"); DBConnection connection = new DBConnection(conn); String sql1 = "select distinct EA_Co_Name from AIC_Access_EventMessageDataAccess"; String sql2 = "select distinct EA_Pe_FirstName from AIC_Access_EventMessageDataAccess"; String sql3 = "select distinct EA_eventName from AIC_Access_EventMessageDataAccess"; ResultSet rs1 = connection.execQuery(sql1); ResultSet rs2 = connection.execQuery(sql2); ResultSet rs3 = connection.execQuery(sql3); this.cnameMap = new LinkedMap(); this.penameMap = new LinkedMap(); this.eventnameMap = new LinkedMap(); while (rs1.next()) { this.cnameMap.put(rs1.getString(1), rs1.getString(1)); } while (rs2.next()) { this.penameMap.put(rs2.getString(1), rs2.getString(1)); } while (rs3.next()) { this.eventnameMap.put(rs3.getString(1), rs3.getString(1)); } connectionManager.freeConnection("namidoor", conn); return SUCCESS; } catch (Exception e) { e.printStackTrace(); return ERROR; } } // 查询结果管理方法 public String doorManage() { try { ResultSet rs = this.getResultSet(); List list = new ArrayList(); while (rs.next()) { NamiDoorEvent de = new NamiDoorEvent(); de.setEA_InsertDateTime(rs.getTimestamp(1)); de.setEA_Co_Name(rs.getString(2)); de.setEA_eventName(rs.getString(3)); de.setEA_Pe_FirstName(rs.getString(4)); de.setEA_Pe_Code(rs.getString(5)); de.setEA_Pe_De_Name(rs.getString(6)); list.add(de); } Map req = (Map) ActionContext.getContext().get("request"); req.put("list", list); req.put("cname", this.cname); req.put("pename", this.pename); req.put("eventname", this.eventname); req.put("btime", this.btime); req.put("etime", this.etime); return SUCCESS; } catch (Exception e) { e.printStackTrace(); return ERROR; } } // 导出报表方法 public String exportDoor() { try { ResultSet rs = this.getResultSet(); // 获取总列数 int CountColumnNum = rs.getMetaData().getColumnCount(); int i = 1; // 创建Excel文档 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("门禁查询"); // 设置单元格宽度 sheet.setColumnWidth(0, 6000); sheet.setColumnWidth(1, 4000); sheet.setColumnWidth(2, 6000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 5000); sheet.setColumnWidth(5, 4000); HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始 HSSFCell[] firstcell = new HSSFCell[CountColumnNum]; String[] names = new String[CountColumnNum]; names[0] = "时间"; names[1] = "控制器名称"; names[2] = "事件名称"; names[3] = "人员"; names[4] = "人员编号"; names[5] = "部门"; for (int j = 0; j < CountColumnNum; j++) { firstcell[j] = firstrow.createCell((short) j); firstcell[j].setCellValue(new HSSFRichTextString(names[j])); } // 填充单元格 while (rs.next()) { // 创建电子表格的一行 HSSFRow row = sheet.createRow(i); // 下标为1的行开始 for (int j = 0; j < CountColumnNum; j++) { // 在一行内循环 HSSFCell cell = row.createCell((short) j); // 设置表格的编码集，使支持中文 // // 先判断数据库中的数据类型 // 将结果集里的值放入电子表格中 cell.setCellValue(new HSSFRichTextString(rs .getString(j + 1))); } i++; } // 设置输出文件名 Date now = new Date(); SimpleDateFormat dateFormat = new SimpleDateFormat( "yyyyMMddHHmmss");// 可以方便地修改日期格式 String nowtime = dateFormat.format(now); this.fileName = "door" + nowtime; // 导出 ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); baos.flush(); byte[] aa = baos.toByteArray(); excelStream = new ByteArrayInputStream(aa, 0, aa.length); baos.close(); System.out.println("数据库导出成功"); return SUCCESS; } catch (Exception e) { //e.printStackTrace(); return ERROR; } } private ResultSet getResultSet() throws IOException, SQLException { ResultSet rs = null; HttpServletRequest request = ServletActionContext.getRequest(); this.cname = request.getParameter("cname"); this.pename = request.getParameter("pename"); this.eventname = request.getParameter("eventname"); this.btime = request.getParameter("btime"); this.etime = request.getParameter("etime"); String sql_top = "select EA_InsertDateTime,EA_Co_Name,EA_eventName,EA_Pe_FirstName,EA_Pe_Code,EA_Pe_DE_Name "; String sql_middle = " from dbo.AIC_Access_EventMessageDataAccess where 1=1"; if (cname != null && !"ALL".equals(cname)) { sql_middle += " and EA_Co_Name=N'" + cname + "'"; } if (!"".equals(pename) && !"ALL".equals(pename) && pename != null) { sql_middle += " and EA_Pe_FirstName=N'" + pename + "'"; } if (pename.equals("")) { sql_middle += " and EA_Pe_FirstName is null"; } if (eventname != null && !"ALL".equals(eventname)) { sql_middle += " and EA_eventName=N'" + eventname + "'"; } if ((btime != null) && (!"".equals(btime))) { sql_middle += " and convert(datetime,'" + btime + "') <= convert(datetime,EA_InsertDateTime)"; } if ((etime != null) && (!"".equals(etime))) { sql_middle += " and convert(datetime,'" + etime + "') >= convert(datetime,EA_InsertDateTime)"; } String sql_end = " order by EA_InsertDateTime desc"; String sql = sql_top + sql_middle + sql_end; DBConnectionManager connectionManager = DBConnectionManager .getInstance(); Connection conn = connectionManager.getConnection("namidoor"); DBConnection connection = new DBConnection(conn); rs = connection.execQuery(sql); connectionManager.freeConnection("namidoor", conn); return rs; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } public String getPename() { return pename; } public void setPename(String pename) { this.pename = pename; } public String getEventname() { return eventname; } public void setEventname(String eventname) { this.eventname = eventname; } public String getBtime() { return btime; } public void setBtime(String btime) { this.btime = btime; } public String getEtime() { return etime; } public void setEtime(String etime) { this.etime = etime; } public Map getCnameMap() { return cnameMap; } public void setCnameMap(Map cnameMap) { this.cnameMap = cnameMap; } public Map getPenameMap() { return penameMap; } public void setPenameMap(Map penameMap) { this.penameMap = penameMap; } public Map getEventnameMap() { return eventnameMap; } public void setEventnameMap(Map eventnameMap) { this.eventnameMap = eventnameMap; } }
package com.sgm.vo; import java.sql.*; public class Demo { public static void main(String args[]) { Connection ct = null; PreparedStatement ps = null; ResultSet rs = null; try { // 1、加载驱动(作用是把需要的驱动程序加入内存) Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // 2、得到连接(不用配置数据源)，驱动名称 ct = DriverManager.getConnection( "jdbc:sqlserver://localhost:1433;databaseName=test", "sa", "123456"); // 3、创建PreparedStatement火箭车 // 执行【如果是增加、删除、修改executeUpdate(),如果是查询则用executeQuery】 ps = ct.prepareStatement("select * from users"); rs = ps.executeQuery(); while (rs.next()) { System.out.println(rs.getString(2) + ";" + rs.getString(3)); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (ps != null)// 如果等于null表明已经关闭 { ps.close(); } if (ct != null)// 如果等于null表明已经关闭 { ct.close(); } } catch (SQLException e) { e.printStackTrace(); } } } }