浅析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;
    }



郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。