几种数据库连接

==================TestSQLServer.java连接SQLServer====================================


import java.sql.*;
import java.util.*;
public class TestSQLServer{  
   public static void main (String args[]){          
      try{  
  Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
  String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=testdb";
  String user ="sa";
  String password = "sa";  
  Connection conn = DriverManager.getConnection(url,user,password);       
         Statement stat = conn.createStatement();
         ResultSet result = stat.executeQuery("SELECT * FROM users");         
         result.next();         
         System.out.println(result.getString(1));
         System.out.println(result.getString(2));
         System.out.println(result.getString(3));         
         result.close();      
         stat.close();
         conn.close();
         
      }catch(ClassNotFoundException en){
        System.out.println("数据库驱动找不到！");
        en.printStackTrace();      
      }catch(SQLException ex) {        
         while (ex != null) {  
            ex.printStackTrace();
            ex = ex.getNextException();
         }         
      }catch(Exception e){
          System.out.println("其他未知异常！");
          e.printStackTrace();
      }
   }
}




=================TestMysql.java连接mysql数据库=======================================


package org.binbo.dom;
import java.sql.*;
public class TestMysql{  
   public static void main (String args[]){          
      try{  
  Class.forName("com.mysql.jdbc.Driver").newInstance();  
  String url = "jdbc:mysql://localhost:3306/binbo";
  String user ="root";
  String password = "binbo";  
  Connection conn = DriverManager.getConnection(url,user,password);
  
        Statement stat = conn.createStatement();
         ResultSet result = stat.executeQuery("SELECT * FROM testxml");         
         result.next();         
         System.out.println(result.getString(1));
         System.out.println(result.getString(2));
         System.out.println(result.getString(3));         
         result.close();      
         stat.close();
         conn.close();         
      }catch(ClassNotFoundException en){
        System.out.println("数据库驱动找不到！");
        en.printStackTrace();      
      }catch(SQLException ex) {        
         while (ex != null) {  
            ex.printStackTrace();
            ex = ex.getNextException();
         }         
      }catch(Exception e){
          System.out.println("其他未知异常！");
          e.printStackTrace();
      }
   }


}
=================TestOracle.java连接oracle数据库==================================


package org.binbo.dom;
import java.sql.*;
public class TestOracle{  
   public static void main (String args[]){          
      try{  
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();  
      String url = "jdbc:oracle:thin:@localhost:1521:binbo";
      String user ="scott";
      String password = "tiger";   
  Connection conn = DriverManager.getConnection(url,user,password);
  
        Statement stat = conn.createStatement();
         ResultSet result = stat.executeQuery("SELECT * FROM test");         
         result.next();         
         System.out.println(result.getString(1));
         System.out.println(result.getString(2));
         System.out.println(result.getString(3));         
         result.close();      
         stat.close();
         conn.close();         
      }catch(ClassNotFoundException en){
        System.out.println("数据库驱动找不到！");
        en.printStackTrace();      
      }catch(SQLException ex) {        
         while (ex != null) {  
            ex.printStackTrace();
            ex = ex.getNextException();
         }         
      }catch(Exception e){
          System.out.println("其他未知异常！");
          e.printStackTrace();
      }
   }
}
===============XML连接数据库=====================================================
da.xml
<?xml version="1.0" encoding="UTF-8"?>
<PEOPLE><!--
  <PERSON PERSONID="E01">
    <className>com.microsoft.jdbc.sqlserver.SQLServerDriver</className>
    <url>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=binbo</url>
    <user>sa</user>
    <password>sa</password>
  </PERSON>
  --><PERSON PERSONID="E02">
     <className>com.mysql.jdbc.Driver</className>
    <url>jdbc:mysql://localhost:3306/binbo</url>
    <user>root</user>
    <password>binbo</password>
  </PERSON><!--
 <PERSON PERSONID="E03">
     <className>oracle.jdbc.driver.OracleDriver</className>
    <url>jdbc:oracle:thin:@localhost:1521:binbo</url>
    <user>scott</user>
    <password>tiger</password>
  </PERSON>
--></PEOPLE>
--------------------------------------------
content.java
package org.binbo.dom;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
public class Content {
 public static Connection getConnection(){
  Connection conn =null;
  try {
   DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
   DocumentBuilder builder=factory.newDocumentBuilder();
   Document doc=builder.parse("da.xml");
   NodeList nl=doc.getElementsByTagName("PERSON");
   Element node=(Element) nl.item(0);
   
 String className =node.getElementsByTagName("className").item(0).getFirstChild().getNodeValue();
         
 String url =node.getElementsByTagName("url").item(0).getFirstChild().getNodeValue();
         
 String user =node.getElementsByTagName("user").item(0).getFirstChild().getNodeValue();
        
 String pwd =node.getElementsByTagName("password").item(0).getFirstChild().getNodeValue();        
        Class.forName(className);
        conn =DriverManager.getConnection(url,user,pwd);      
  
  } catch (Exception e) {
   e.printStackTrace();
  }
  return conn;
 } 
}
--------------------------------------------
Domtest.java
package org.binbo.dom;
import java.io.FileOutputStream;
import java.sql.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;


import org.w3c.dom.Document;
import org.w3c.dom.Element;


public class Domtest {
 public static void main(String[] args){
  try{
   Connection conn =Content.getConnection();
   PreparedStatement ps=conn.prepareStatement("select * from testxml");
   ResultSet rs =ps.executeQuery();
   DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
   DocumentBuilder builder = factory.newDocumentBuilder();
   Document doc = builder.newDocument();
   Element Stu = doc.createElement("binboxml");
   Element uname = doc.createElement("username");
   Element nameid = doc.createElement("userid");
   Element pass = doc.createElement("password");
   while(rs.next()){    
    String id=rs.getString(1);
    String name=rs.getString(2);
    String pwd=rs.getString(3);
    System.out.print(rs.getString(1));
    System.out.print(rs.getString(2));
    System.out.print(rs.getString(3));
       System.out.print("写入成功！");
    nameid.appendChild(doc.createTextNode(id));
    uname.appendChild(doc.createTextNode(name));
    pass.appendChild(doc.createTextNode(pwd));
   }
   
   Stu.appendChild(nameid);   
   Stu.appendChild(uname);
   Stu.appendChild(pass);
   doc.appendChild(Stu);
   TransformerFactory tf = TransformerFactory.newInstance();
   Transformer tr = tf.newTransformer();
   tr.transform(new DOMSource(doc), new StreamResult(
     new FileOutputStream("Binbo.xml")));
   rs.close();
  } catch(Exception e){
   e.printStackTrace();
  }


 }


}


=====================数据库连接池=====================================


加到tomcat中的server.xml


<Logger className="org.apache.catalina.logger.FileLogger"
                 directory="logs"  prefix="localhost_log." suffix=".txt"
            timestamp="true"/>


这个后面
<Context path="/myjsp" docBase="myjsp"
        debug="5" reloadable="true" crossContext="true">
       <Resource name="jdbc/myjsp"
               auth="Container"
               type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/myjsp">
    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>
    <parameter>
      <name>maxActive</name>
      <value>100</value>
    </parameter>
    <parameter>
      <name>maxIdle</name>
      <value>30</value>
    </parameter>
    <parameter>
      <name>maxWait</name>
      <value>10000</value>
    </parameter>
 
    <parameter>
     <name>username</name>
     <value>sa</value>
    </parameter>
    <parameter>
     <name>password</name>
     <value>sa</value>
    </parameter>
    <parameter>
       <name>driverClassName</name>
       <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
    </parameter>
    <parameter>
      <name>url</name>
      <value>jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=jsp</value>
    </parameter>
  </ResourceParams>
</Context>
-----------------------------------------------------------------------
用DBPool.java获得数据库连接池
package com.binbo.dbo;
import java.sql.Connection;
import java.sql.DriverManager;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBPool {
 public static synchronized Connection getConnection()throws Exception{
  DataSource ds = null;
  try{
  Context ininCtx  = new javax.naming.InitialContext();
  
  Context envCtx = (Context)ininCtx.lookup("java:comp/env");
    ds = (DataSource)envCtx.lookup("jdbc/myjsp");
  }catch(NamingException e){
   e.printStackTrace();   
  }   
  Connection conn = ds.getConnection();
  return conn;  
 }
}
-----------------------------------------------------------
操作数据库
package com.binbo.dbo;
import java.sql.*;
import com.binbo.javabean.BreakBean;
import com.binbo.javabean.OpenBean;
public class DataBaseClass {
 private Statement sta = null;
 private ResultSet rs = null;
 Connection conn = null;
 private int count;
 public DataBaseClass() throws Exception {
  // 取得数据库的连接
  conn = DBPool.getConnection();
  sta = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_UPDATABLE);
 }
 public void getExecute(String sql) {
  try {
   System.out.println(sql);
   sta.executeUpdate(sql);
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }


 /*
  * sql 语句集的查询
  */
 public ResultSet getQuery(String sql) {
  try {
   System.out.println(sql);
   rs = sta.executeQuery(sql);
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return rs;
 }


 
  * 取得结果集的行数
 
 public int getCount(ResultSet rs2) {
  try {
   rs2.next();
   count = rs2.getRow();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return count;
 }
  //添加！  
 public void getRegister(OpenBean open) {
  String sql = "insert into f_info(nam,email,titl,content,tem,mid)values(?,?,?,?,?,?)";
  try {
   PreparedStatement ps = conn.prepareStatement(sql);
   ps.setString(1, open.getNam());
   ps.setString(2, open.getEmail());
   ps.setString(3, open.getTitl());
   ps.setString(4, open.getContent());
   ps.setString(5, open.getTem());
   ps.setString(6, open.getDepa());
   ps.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
        // 删除
 public void getRealys(OpenBean real) {
  try {
   String sql = "delete from departments where id=?";
   PreparedStatement ppt = conn.prepareStatement(sql);
   ppt.setString(1, real.getUnam());
   ppt.execute();


  } catch (SQLException e) {
   e.printStackTrace();
  }


 }


 // 查询
 public void getRealy(OpenBean hg) {


  try {
   String sql = "select * from departments where id=?";
   PreparedStatement ppg = conn.prepareStatement(sql);
   ppg.setString(1, hg.getUnam());
   ppg.execute();
  } catch (SQLException e) {
   e.printStackTrace();
  }
 }
}
============================hibernate操作数据库====================================
package com.binbo.hibernate.xml;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration; 
public class PersonOperate {
 private Session session = null ;
 public PersonOperate()
 {
  Configuration config = new Configuration().configure() ;
  SessionFactory factory = config.buildSessionFactory() ;
  this.session = factory.openSession() ;
 } 
 // 增加数据
 public void insert(Person p)
 {
  Transaction tran = this.session.beginTransaction() ;
  this.session.save(p) ;
  tran.commit() ;
  this.session.close() ;
 }
------------------------------------------
增加数据调用
                LinOperate po = new LinOperate();
                Lin p = new Lin();
  p.setLname(lname);
  p.setLmonery(lmonery);
  p.setLmain(lmain);
  p.setLtime(ltime);
  LinOperate po = new LinOperate();
  po.insert(p);
  errors.add("success", new ActionMessage("xiangxi"));
  request.setAttribute("org.apache.struts.action.ERROR", errors);
  return mapping.findForward("jinru");
------------------------------------------
 
 // 修改
 public void update(Person p)
 {
  Transaction tran = this.session.beginTransaction() ;
  this.session.update(p) ;
  tran.commit() ;
  this.session.close() ;
 } 
 // 用户登录
 public boolean queryById(Person person)
 {
  boolean flag = false;
  String hql = "from Person as p where p.id=? and p.password=?" ;
  Query q = this.session.createQuery(hql) ;
  q.setString(0,person.getId()) ;
  q.setString(1,person.getPassword());
  Iterator iter = q.list().iterator();
  if (iter.hasNext()) {
   flag = true;
   person.setName(((Person) iter.next()).getName());
   
  }
  this.session.close() ;
  return flag;
 }
 //验证用户存不存在
 public boolean queryC(Person person)
 {
  boolean flag = false;
  String hql = "from Person as p where p.name=?" ;
  Query q = this.session.createQuery(hql) ;
  q.setString(0,person.getName()) ;
  Iterator iter = q.list().iterator();
  if (iter.hasNext()) {
   flag = true;   
  }
  this.session.close() ;
  return flag;
 }
 //查看个人资料
 public Person queryZliao(String id)
 {
  Person p = null ;
  String hql = "from Person as p where p.id=?" ;
  Query q = this.session.createQuery(hql) ;
  q.setString(0,id) ;
  List l = q.list() ;
  Iterator iter = l.iterator() ;
  if(iter.hasNext())
  {
   p = (Person)iter.next() ;
  }
  return p ;
 } 
 // 删除数据
 public void delete(Person p)
 {
  Transaction tran = this.session.beginTransaction() ;
  this.session.delete(p) ;
  tran.commit() ;
 } 
 // 修改
 public void delete(String name)
 {
  String hql = "delete Person where name=?" ;
  Query q = this.session.createQuery(hql) ;
  q.setString(0,name) ;
  q.executeUpdate() ;
  this.session.beginTransaction().commit() ;
 }
 
 // 查询全部数据
 public List queryAll()
 {
  List l = null ;
  String hql = "from Person as p" ;
  Query q = this.session.createQuery(hql) ;
  l = q.list() ;
  return l ;
 }
-------------------------------
(
查询全部数据调用
                LinOperate po = new LinOperate();
  List l = po.queryAll();
  Iterator iter = l.iterator();
  ArrayList lus = new ArrayList();
  while (iter.hasNext()) {
   Lin p = (Lin) iter.next();
   Luser lu = new Luser();
   lu.setName(p.getLname());
   lu.setChange(p.getLmonery());
   lu.setMainn(p.getLmain());
   lu.setLtime(p.getLtime());
   lus.add(lu);
   System.out.print(p.getLname() + " ");
   request.setAttribute("lus", lus);




)
------------------------ 
 // 模糊查询
 public List queryByLike(String cond)
 {
  List l = null ;
  String hql = "from Person as p where p.name like ?" ;
  Query q = this.session.createQuery(hql) ;
  q.setString(0,"%"+cond+"%") ;
  l = q.list() ;
  return l ;
 }
}