Java_DBUtil

最新推荐文章于 2022-02-14 10:55:33 发布

Jzgl

最新推荐文章于 2022-02-14 10:55:33 发布

阅读量126

点赞数

分类专栏： Util 文章标签： Java JDBC SQL DB2 SQL Server

Util 专栏收录该内容

3 篇文章

订阅专栏


/**
*DBUtil.java
*连接数据库
*/
import java.io.*;
import java.sql.*;
import java.util.Properties;
public class Db {
    private String driver;//定义驱动
    private String url;//定义URL
    private String user;//定义用户名
    private String password;//定义密码
    private Connection conn;//定义连接
    private Statement stmt;//定义STMT
    private ResultSet rs;//定义结果集
    //构造函数，默认加裁配置文件为jdbc.driver
    public Db(){
        this("jdbc.properties");
    }
    //有参构造函数，传入路径Conf并用方法loadProperties进行加载，用setConn进行设置
    public Db(String conf) {
        loadProperties(conf);
        setConn();
    }
    //返回Conn
    public Connection getConn(){
        return this.conn;
    }
  //传入参数路径Conf加载配置文件取得配置文件中的参数并设置为类变量的参数
    private void loadProperties(String conf){
        Properties props = new Properties();
        try {
            props.load(new FileInputStream(conf));//根据配置文件路径Conf加载配置文件
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        this.driver = props.getProperty("driver");//从配置文件中取得相应的参数并设置类变量
        this.url = props.getProperty("url");
        this.user = props.getProperty("username");
        this.password = props.getProperty("password");
    }
    //implement the Connection
    //设置CONN
    private void setConn(){
        try {
            Class.forName(driver);
            this.conn = DriverManager.getConnection(url,user,password);
        } catch(ClassNotFoundException classnotfoundexception) {
              classnotfoundexception.printStackTrace();
            System.err.println("db: " + classnotfoundexception.getMessage());
        } catch(SQLException sqlexception) {
            System.err.println("db.getconn(): " + sqlexception.getMessage());
        }
    }
    //执行插入
       public void doInsert(String sql) {
        try {
            Statement statement = conn.createStatement();
            int i = stmt.executeUpdate(sql);
        } catch(SQLException sqlexception) {
            System.err.println("db.executeInset:" + sqlexception.getMessage());
        }finally{

        }
    }
    //执行删除
    public void doDelete(String sql) {
        try {
            stmt = conn.createStatement();
            int i = stmt.executeUpdate(sql);
        } catch(SQLException sqlexception) {
            System.err.println("db.executeDelete:" + sqlexception.getMessage());
        }
    }
    //执行更新
    public void doUpdate(String sql) {
        try {
            stmt = conn.createStatement();
            int i = stmt.executeUpdate(sql);
        } catch(SQLException sqlexception) {
            System.err.println("db.executeUpdate:" + sqlexception.getMessage());
        }
    }
    //查询结果集
    public ResultSet doSelect(String sql) {
        try {
            stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(sql);
        } catch(SQLException sqlexception) {
            System.err.println("db.executeQuery: " + sqlexception.getMessage());
        }
        return rs;
    }
    /**
     *关闭数据库结果集，数据库操作对象，数据库链接
       @Function: Close all the statement and conn int this instance and close the parameter ResultSet
       @Param: ResultSet
       @Exception: SQLException,Exception
      **/
     public void close(ResultSet rs) throws SQLException, Exception {

       if (rs != null) {
         rs.close();
         rs = null;
       }

       if (stmt != null) {
         stmt.close();
         stmt = null;
       }

       if (conn != null) {
         conn.close();
         conn = null;
       }
     }

     /**
      *关闭数据库操作对象，数据库连接对象
      * Close all the statement and conn int this instance
      * @throws SQLException
      * @throws Exception
      */
     public void close() throws SQLException, Exception {
       if (stmt != null) {
         stmt.close();
         stmt = null;
       }

       if (conn != null) {
         conn.close();
         conn = null;
       }
     }
    //测试类
    public static void main(String[] args){
        try{
        	//根据传入配置路径构造类
            Db db = new Db("src\\com\\struts\\properties\\jdbc.properties");
//            db.loadProperties("src\\com\\struts\\properties\\jdbc.properties");
//            System.out.print(db.driver);
            //取得连接
            Connection conn = db.getConn();
            if(conn != null && !conn.isClosed()) {
                System.out.println("連結成功");
                ResultSet rs = db.doSelect("select * from usertable");
                while(rs.next()){
                    System.out.println(rs.getString(1)+":"+rs.getString(2)+":"+rs.getString(3));
                  }
                rs.close();
                conn.close();
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }
    }  
}


//配置文件jdbc.properties
driver=com.microsoft.jdbc.sqlserver.SQLServerDriver
url=jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=qtliu
username=sa
password=sa



MySQL：    
    String Driver="com.mysql.jdbc.Driver";    //驱动程序
    String URL="jdbc:mysql://localhost:3306/db_name";    //连接的URL,db_name为数据库名    
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).new Instance();
    Connection con=DriverManager.getConnection(URL,Username,Password);
Microsoft SQL Server 2.0驱动(3个jar的那个):
    String Driver="com.microsoft.jdbc.sqlserver.SQLServerDriver";    //连接SQL数据库的方法
    String URL="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=db_name";    //db_name为数据库名
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).new Instance();    //加载数据可驱动
    Connection con=DriverManager.getConnection(URL,UserName,Password);    //
Microsoft SQL Server 3.0驱动(1个jar的那个): // 老紫竹完善
    String Driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";    //连接SQL数据库的方法
    String URL="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=db_name";    //db_name为数据库名
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).new Instance();    //加载数据可驱动
    Connection con=DriverManager.getConnection(URL,UserName,Password);    //
Sysbase:
    String Driver="com.sybase.jdbc.SybDriver";    //驱动程序
    String URL="jdbc:Sysbase://localhost:5007/db_name";    //db_name为数据可名
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).newInstance();    
    Connection con=DriverManager.getConnection(URL,Username,Password);
Oracle(用thin模式):
    String Driver="oracle.jdbc.driver.OracleDriver";    //连接数据库的方法
    String URL="jdbc:oracle:thin:@loaclhost:1521:orcl";    //orcl为数据库的SID
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).newInstance();    //加载数据库驱动
    Connection con=DriverManager.getConnection(URL,Username,Password);    
PostgreSQL:
    String Driver="org.postgresql.Driver";    //连接数据库的方法
    String URL="jdbc:postgresql://localhost/db_name";    //db_name为数据可名
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).newInstance();    
    Connection con=DriverManager.getConnection(URL,Username,Password);
DB2：
    String Driver="com.ibm.db2.jdbc.app.DB2.Driver";    //连接具有DB2客户端的Provider实例
    //String Driver="com.ibm.db2.jdbc.net.DB2.Driver";    //连接不具有DB2客户端的Provider实例
    String URL="jdbc:db2://localhost:5000/db_name";    //db_name为数据可名
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).newInstance();    
    Connection con=DriverManager.getConnection(URL,Username,Password);
Informix:
    String Driver="com.informix.jdbc.IfxDriver";    
    String URL="jdbc:Informix-sqli://localhost:1533/db_name:INFORMIXSER=myserver";    //db_name为数据可名
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).newInstance();    
    Connection con=DriverManager.getConnection(URL,Username,Password);
JDBC-ODBC:
    String Driver="sun.jdbc.odbc.JdbcOdbcDriver";
    String URL="jdbc:odbc:dbsource";    //dbsource为数据源名
    String Username="username";    //用户名
    String Password="password";    //密码
    Class.forName(Driver).newInstance();    
    Connection con=DriverManager.getConnection(URL,Username,Password);

package utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnection {

static Connection conn=null;
static PreparedStatement ps=null;
static ResultSet rs=null;

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

} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

//静态方式取得链接
public static Connection getConn(){
String uname="sa";
String passwd="";
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=qtliu";
try {
conn=DriverManager.getConnection(url,uname,passwd);
System.out.println("GetConnection success");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}


//释放资源
public static void release(){

if(rs!=null){
try {
rs.close();
System.out.println("rs Closed!");
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
System.out.println("ps Closed!");
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
System.out.println("conn Closed!");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}




=====================
package utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestGetConn {

 /**
  * @param args
  */
 public static void main(String[] args) {
  Connection conn=DBConnection.getConn();
  try {
   Statement st=conn.createStatement();
   String find="select *from qt";
   ResultSet rs=st.executeQuery(find);
   while(rs.next()){
    System.out.println("name:"+rs.getString(2)+"\n");
    System.out.println("passwd:"+rs.getString(3));
   }
   System.out.println();
   DBConnection.release();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }


 }

}