jdbc连接database封装

JDBC工作过程

连接sql2005必须先导入一个名为sqljdbc.jar的外部jar包。

1. 引入一个必要的包

 import java.sql.*;

2. 加载JDBC驱动程序

Class.forName(com.microsoft.sqlserver.jdbc.SQLServerDriver);

3.标识数据源（URL、Username、Password）

4. 分配一个Connection对象  

5.分配一个PreparedStatement对象,执行检查 SQL语句 

6.从返回的ResultSet对象中检索数据,类似迭代器

executeQuery()——执行返回结果集的select语句，返回ResultSet型。
executeUpdate()——执行不返回ResultSet对象的查询。它的返回的受影响的行数，返回int型。
execute()——判断是否返回结果集，返回boolean型。

 

外部jar包:

文件:	jar-sql2005.rar
大小:	551KB
下载:	下载

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BaseDAO
{
    private final String DRIVER = "com.mysql.jdbc.Driver";
    private final String URL = "jdbc:mysql://127.0.0.1:3306/database";
    private final String USER = "username";
    private final String PWD = "password";

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    public Connection getCon()
    {
        try
        {
            Class.forName(DRIVER);
        }
        catch (ClassNotFoundException e)
        {
            System.out.println("加载驱动异常！");
            e.printStackTrace();
        }
        try
        {
            con = DriverManager.getConnection(URL, USER, PWD);
        }
        catch (SQLException e)
        {
            System.out.println("获得数据库连接失败！");
            e.printStackTrace();
        }
        return con;
    }

    public ResultSet executeQuery(String sql, List list)
    {
        con = this.getCon();
        try
        {
            pstmt = con.prepareStatement(sql);
            if (list != null)
            {
                this.setValue(pstmt, list);
            }
            rs = pstmt.executeQuery();
        }
        catch (SQLException e)
        {
            System.out.println("查询失败！");
            e.printStackTrace();
        }
        return rs;
    }

    public int executeUpdate(String sql, List list)
    {
        con = this.getCon();
        int i = 0;

        try
        {
            pstmt = con.prepareStatement(sql);
            if (list != null)
            {
                this.setValue(pstmt, list);
            }
            i = pstmt.executeUpdate();
        }
        catch (SQLException e)
        {
            System.out.println("更新失败！");
            e.printStackTrace();
        }
        finally
        {
            this.closeAll();
        }

        return i;
    }

    public void setValue(PreparedStatement pstmt, List list)
    {
        for (int i = 0; i < list.size(); i++)
        {
            try
            {
                pstmt.setObject(i + 1, list.get(i));
            }
            catch (SQLException e)
            {
                System.out.println("设置参数失败！");
                e.printStackTrace();
            }
        }
    }

    public void closeAll()
    {
        if (null != rs)
        {
            try
            {
                rs.close();
            }
            catch (SQLException e)
            {
                System.out.println("关闭rs失败！");
                e.printStackTrace();
            }
        }

        if (null != pstmt)
        {
            try
            {
                pstmt.close();
            }
            catch (SQLException e)
            {
                System.out.println("关闭pstmt失败！");
                e.printStackTrace();
            }
        }

        try
        {
            if (null != con && (!con.isClosed()))
            {
                con.close();
            }
        }
        catch (SQLException e)
        {
            System.out.println("关闭con失败！");
            e.printStackTrace();
        }
    }

}