浅析JDBC



以连接MS SQL Server为例

    

 步骤：

1 创建项目

2 导入数据库的驱动jar包   

3 在src下面创建四个包 

  db :连接数据库的工具类

     连接数据库需要四个条件：

        1、DRIVER_STRING，连接数据库驱动

        2、UR_STRING，URL地址，并且设定连接到哪个数据库

        3、UER_STRING，User用户名

        4、PA_STRING，Password密码

     以及3个步骤

　　

     1 加载数据库驱动字符串 

     2 获得和数据库的连接    static

     3 关闭数据库连接的方法   static

 

     因为方法是静态的  所有当我们需要获得连接的时候   可以直接通过类名.方法名直接调用

  dto：数据传输模型       

       如果数据库中有一张表，那么这时候就有DTO 

      每一个DTO字段的类型及其字段的个数       都和对应表中的个数及其类型是一致的 

      例如：people表  （pid   pname  psex）

      DTO 中  也有三个属性

 

  dao： 增   删  改   查 

  以添加来说

      1 获得和数据库的连接 

      2 准备SQL语句 

      3 获得执行SQL的命令

      4 给问号赋值

      5 执行SQL

      6 关闭连接

      7 释放资源

举例：

1.数据库连接字段

<strong>private static final String DRIVER_STRING="com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String UR_STRING="jdbc:sqlserver://localhost:1433;" +
            "databaseName=javateam;integratedSecurity=true;";
    private static final String UER_STRING="sa";
    private static final String PA_STRING="joy19940521";
    //加载数据库驱动的静态方法
    static{
        try {
            Class.forName(DRIVER_STRING);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }</strong>

2.获得连接的方法

<strong>public static Connection getConnection(){
        Connection connection=null;
        try {
            connection=DriverManager.getConnection(UR_STRING, UER_STRING, PA_STRING);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return connection;
    }</strong>

3.关闭连接的方法

<strong>public Connection dropConnection(Connection connection,
            PreparedStatement preparedStatement,
            ResultSet resultSet){
        if (connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }if (preparedStatement!=null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }if (resultSet!=null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            
        }
        return null;
        
    }</strong>

4.增删改查

分别由不同的方法实现。

（1）add

public static void add(People people){
        //1、获得和数据库的连接
        connection = DBconnection.getcConnection();
        //2、准备SQL语句
        String sql = "insert into people(pname,psex)values(?,?)";
        //3、准备状态，获得执行SQL的命令
        try {
            pStatement = connection.prepareStatement(sql);
            //4、给问号赋值
            pStatement.setString(1, people.getPname());
            pStatement.setString(2, people.getPsex());
            
            int i = pStatement.executeUpdate();
            if (i>0) {
                System.out.println("添加成功");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBconnection.close(connection, pStatement, null);
        }
    }

（2）delete

public static void delete(int pid){
        connection = DBconnection.getcConnection();
        String sqlString = "delete from people where pid=?";
        try {
            pStatement = connection.prepareStatement(sqlString);
            pStatement.setInt(1, pid);
            
            int j = pStatement.executeUpdate();
            if (j>0) {
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBconnection.close(connection, pStatement, null);
        }    
    }

（3）update

public static void update(String pname,String psex,int pid){
        connection=DBconnection.getcConnection();
        String sql = "update people set pname=?,psex=? where pid = ?";
        
        try {
            pStatement = connection.prepareStatement(sql);
            pStatement.setString(1, pname);
            pStatement.setString(2, psex);
            pStatement.setInt(3, pid);
                
            int y = pStatement.executeUpdate();
            
            if (y>0) {
                System.out.println("更新成功");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBconnection.close(connection, pStatement, null);
        }
        
    }

（4）查询

//根据pid查询
    public static People findByID(int pid){
        People people = null;
        connection = DBconnection.getcConnection();
        
        String sql = "select * from people where pid = ?";
        try {
            pStatement = connection.prepareStatement(sql);
            pStatement.setInt(1, pid);
            //执行
            rSet = pStatement.executeQuery();
            //对结果集进行遍历
            while (rSet.next()) {
                int id = rSet.getInt("pid");
                String name = rSet.getString("pname");
                String sex = rSet.getString("psex");
                //生成ID查询对象
                people = new People(id, name, sex);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBconnection.close(connection, pStatement, rSet);
        }
        return people;
    }
    //查询表中所以内容
    public static List<People> findAll(){
        People people = null;
        List<People> list = new ArrayList<People>();
        
        connection = DBconnection.getcConnection();
        
        String sql = "select * form people";
        
        try {
            pStatement = connection.prepareStatement(sql);
            rSet = pStatement.executeQuery();
            
            while(rSet.next()){
                int id = rSet.getInt("pid");
                String name = rSet.getString("pname");
                String sex = rSet.getString("psex");
                //生成ID查询对象
                people = new People(id, name, sex);
                list.add(people);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            DBconnection.close(connection, pStatement, rSet);
        }
        return list;
    }