java编写连接数据库代码

1、把数据库连接信息（用户名，密码等等）保存早配置文件jdbc.properties中。如下jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=BBS

jdbc.username=sa

jdbc.password=ddme123

2、之后创建DBConfig.java提取在jdbc.properties中保存的属性值。如下：

package com.dedo.BBS.Jdbc;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.Properties;

public class DBConfig {

String driver;

String url;

String username;

String password;//在jdbc.properties中保存的值

public String getDriver() {//添加get方法

return driver;

}

public String getUrl() {

return url;

}

public String getUsername() {

return username;

}

public String getPassword() {

return password;

}

public DBConfig() {//在构造函数中赋值

super();

InputStream is = null;

try {

is = new FileInputStream("D:\\jdbc.properties");

Properties p = new   Properties();

p.load(is);

this.driver = p.getProperty("jdbc.driver");//对当前类对应的值赋值

this.url = p.getProperty("jdbc.url");

this.username = p.getProperty("jdbc.username");

this.password = p.getProperty("jdbc.password");

} catch (FileNotFoundException e) {

e.printStackTrace();

}catch (IOException e) {

e.printStackTrace();

}finally{

if(is != null)

{

try {

is.close();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

}

3、 把SQL语句封装成一个常量类，便于开发和维护。

package com.dedo.BBS.Jdbc;

public class SQLConstant {

public static final String addmessage = "INSERT INTO BBSMessage(auther,info,published) VALUES(?,?,?)";//添加公告信息

public static final String getmessage = "SELECT * FROM BBSMessage";//获取公告信息

}

4、由于数据库很多操作是重复的，所以可以把重复的操作建立一个通用操作类JDBCOption.java，并且添加jar包。可以减少重复操作，标准化数据操作。操作分两大类，其中update（）用于增删改，query（）用于查。如下：

package com.dedo.BBS.Jdbc;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import com.dedo.BBS.Tool.Common;

public class JDBCOption {

private static DBConfig dbconfig = null;

static {

dbconfig = new DBConfig();

try {

Class.forName(dbconfig.getDriver());

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static int update(String sql, Object... paramters) {

int returnValue = 0;

Connection conn = null;

PreparedStatement ps = null;

try {

conn = DriverManager.getConnection(dbconfig.getUrl(), dbconfig.getUsername(), dbconfig.getPassword());

ps = conn.prepareStatement(sql);

for (int i = 0; i < paramters.length; i++) {

ps.setObject(i + 1, paramters[i]);

}

returnValue = ps.executeUpdate();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

Common.free(conn, ps, null);

}

return returnValue;

}

public static List query(String sql, Object... paramters) {

List returnValue = new ArrayList();

ResultSet rs = null;

Connection conn = null;

PreparedStatement ps = null;

try {

conn = DriverManager.getConnection(dbconfig.getUrl(), dbconfig.getUsername(), dbconfig.getPassword());

ps = conn.prepareStatement(sql);

for (int i = 0; i < paramters.length; i++) {

ps.setObject(i + 1, paramters[i]);

}

rs = ps.executeQuery();

while (rs.next()) {

ResultSetMetaData md = rs.getMetaData();

Map map = new HashMap();

for (int i = 0; i < md.getColumnCount(); i++) {

map.put(md.getColumnLabel(i + 1), rs.getObject(i + 1));

}

returnValue.add(map);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}finally{

Common.free(conn, ps, rs);

}

return returnValue;

}

}

 

 

5、为tool中创建common.java,其中一些通用操作，比如关闭数据库

package com.dedo.BBS.Tool;

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

public class Common {
 
 public static void free(Connection conn, Statement sm, ResultSet rs) {
  if (rs != null) {
   try {
    rs.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
   if (sm != null) {
    try {
     sm.close();
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }

   }
   if (conn != null) {
    try {
     conn.close();
    } catch (SQLException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }

   }

  }
 }
}