写在JDBC前面的话_com.cissst.dao.impl.in-CSDN博客

本文链接：https://blog.csdn.net/u011685720/article/details/46573503

我是开篇的小尾巴~~~~

做一个项目首先要有明确的思路和分包的原则

可以有点废话以下

jdbc从建立映像再到进行操作可以放在一个同一个类下面进行操作但是如果分包放在一个项目之下则可

以进到最大化的数据操作和保护

思路

首先根据所需要的操作的表的数量确定需要建立的包（一个包下最好建立一个同名的类将便利最大化）

的多少原则是一个表对应一个类列为类的属性而数值就像是属性的值最重要的就是需要根据需要找

出在中间起到关联作用的那个表然后将此表作为标准接口化然后新建一个包下创建类实现此接口以获

取所需要返回的具体对象或者值之后再将其遍历或者进行其他操作

下面是简单分包的例子

具体可以按照下面5个步骤来写

JDBC操作：

1.  获得连接对象
    A.  将%oracle_home%\product\10.2.0\db_1\jdbc\lib的ojdbc14.jar
    或者classic12.jar拷贝到当前项目的根目录并添加到buildPath中
    B.  分别通过四个字符串url，driver， user， password获得连接对象
        a. Class.forName(driver);
        b. Connection conn = DriverManager.getConnection(url, user, password);  
2.  编写sql语句
        String sql1 = "select *  from dept";
        String sql2 = "insert into dept values(50, 'test1', 'test1')";
3.  构建PreparedStatement对象
        PreparedStatement ptst = conn.preparedStatement(sql);
4.  执行操作
        查询操作
            ResultSet rs = ptst.executeQuery();

            while(rs.next){
                int deptNo = rs.getInt(1); // rs.getInt("deptNo");
                ...
            }

        增删改：
            int num = ptst.executeUpdate();
            if(num > 0)
                success;
5.  关闭所有的对象
        rs， ptst, conn

以下是实例

eg：注册驱动

package com.cissst.utils;

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

public class DBManager {

    private static final String DB_URL 
     = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static final String DB_DRIVER  
    = "oracle.jdbc.driver.OracleDriver";
    private static final String DB_USER = "scott";
    private static final String DB_PASS = "tiger";

    private static Connection conn;

    public static Connection getConnection(){

        try {
            Class.forName(DB_DRIVER);
            conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    public static void closeAll(ResultSet rs,  PreparedStatement ptst, Connection conn){
        try {
            if(rs!= null){
                rs.close();
            }
            if(ptst!= null){
                ptst.close();
            }
            if(conn!= null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

设置表的类

package com.cissst.entity;

public class Dept {

    private int deptno;
    private String dname;
    private String loc;

    public int getDeptno() {
        return deptno;
    }

    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }
}

设置接口  确定方法  并且返回需要的list列表

package com.cissst.dao;

import java.util.List;

import com.cissst.entity.Dept;

public interface IDeptDao {

    List<Dept> getAllDept();

    boolean updateDept(Dept dept);
}


package com.cissst.dao.impl;

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

import com.cissst.dao.IDeptDao;
import com.cissst.entity.Dept;
import com.cissst.utils.DBManager;

public class DeptDaoImpl implements IDeptDao {

    private Connection conn;
    private PreparedStatement ptst;
    private ResultSet rs;


    @Override
    public List<Dept> getAllDept() {

        List<Dept> list = new ArrayList<Dept>();

        conn = DBManager.getConnection();

        String sql= "select * from dept order by deptno";
        try {
            ptst = conn.prepareStatement(sql);

            rs = ptst.executeQuery();

            Dept dept = null;
            while(rs.next()){
                dept= new Dept();

                dept.setDeptno(rs.getInt(1));
                dept.setDname(rs.getString(2));
                dept.setLoc(rs.getString(3));

                list.add(dept);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBManager.closeAll(rs, ptst, conn);
        }

        return list;
    }

    @Override
    public boolean updateDept(Dept dept) {
        // TODO Auto-generated method stub
        return false;
    }

}


这个是简单的测试包
package com.cissst.test;

import java.util.List;

import com.cissst.dao.IDeptDao;
import com.cissst.dao.impl.DeptDaoImpl;
import com.cissst.entity.Dept;

public class Test {

    public static void main(String[] args) {
        IDeptDao deptDao = new DeptDaoImpl();

        List<Dept> list = deptDao.getAllDept();

        System.out.println("deptno\tdname\tloc");
        for (Dept dept : list) {
            System.out.println(dept.getDeptno() + "\t" 
            + dept.getDname() + "\t" + dept.getLoc());
        }
    }
}

这里另外附上几个实例的简单代码可能有些乱没办法回路短处理不来 = =

eg:

以下示例为一个类中进行最简单的数据操作

package com.cissst;

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

public class AddDept {

    /**
     * @param args
     */
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

            String sql = "insert into dept values(50, 'test1', 'test1')";

            PreparedStatement ptst = conn.prepareStatement(sql); 
            //也可以用statement 不过prepared可以写带问号的sql语句 
     //具体格式是 Statement sta = new Statement();int num =sta.executequery(sql);

            int num = ptst.executeUpdate();

            if (num > 0) {
                System.out.println("update successfully.");
            } else {
                System.out.println("update failured.");
            }

            if(ptst != null){
                ptst.close();//此处应该如此写 并且弄准确异常的抛出 
// 一般throws异常  则不会进行处理 并且在错误异常时 后续的操作不会进行 所以try catch好一些
            }

            if(conn != null){
                conn.close();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

示例二 

package com.cissst;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class AddDept2 {

    /**
     * @param args 此示例是将输入操作和数据处理联合一起  重点是注意在写sql语句时的变量带
     */入
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

            Scanner input = new Scanner(System.in);

            System.out.println("请输入部门编号：");
            int deptNo = input.nextInt();
            System.out.println("请输入部门名称：");
            String dname = input.next();
            System.out.println("请输入部门位置：");
            String loc = input.next();

//          String sql = "insert into dept values(50, 'test1', 'test1')";
            String sql = "insert into dept values(" + deptNo + ", '" + dname+ "', '" + loc +"')";

            PreparedStatement ptst = conn.prepareStatement(sql);

            int num = ptst.executeUpdate();

            if (num > 0) {
                System.out.println("update successfully.");
            } else {
                System.out.println("update failured.");
            }

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

            if(conn != null){
                conn.close();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

示例三

package com.cissst;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class AddDept3 {

    /**
     * @param args 此示例则是测试？？带入的写法
     */
    public static void main(String[] args) {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

            Scanner input = new Scanner(System.in);

            System.out.println("请输入部门编号：");
            int deptNo = input.nextInt();
            System.out.println("请输入部门名称：");
            String dname = input.next();
            System.out.println("请输入部门位置：");
            String loc = input.next();
**重点内容**
//          String sql = "insert into dept values(50, 'test1', 'test1')";
            String sql = "insert into dept values(?, ?, ?)";

            PreparedStatement ptst = conn.prepareStatement(sql);
    //将sql封装之后  但是sql语句是不完整的 所以需要用下列方法将语句的各项设置完整
            ptst.setInt(1, deptNo);
            ptst.setString(2, dname);
            ptst.setString(3, loc);

            int num = ptst.executeUpdate();

            if (num > 0) {
                System.out.println("update successfully.");
            } else {
                System.out.println("update failured.");
            }

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

            if(conn != null){
                conn.close();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

示例四

package com.cissst;

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

public class GetAllDept {

    /**
     * @param args
     */
    public static void main(String[] args) {

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", 
            "scott", "tiger");

            Scanner input = new Scanner(System.in);
            System.out.println("请输入要查询的部门编号:");
            int dNo = input.nextInt();

            String sql = "select * from dept where deptno = ? order by deptno";

            PreparedStatement ptst = conn.prepareStatement(sql);

            ptst.setInt(1, dNo);

            ResultSet rs = ptst.executeQuery();

            System.out.println("deptno\tdname\tloc");
            while(rs.next()){
                int deptNo = rs.getInt("deptNo");
                String dName = rs.getString("dName");
                String loc = rs.getString("loc");

                System.out.println(deptNo + "\t" + dName + "\t" + loc);
            }

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

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

            if(conn != null){
                conn.close();
            }
            System.out.println("query successfully!");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

另外的小例子

package com.cissst.utils;

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

/**
 * 公共的连接数据库的类
 * @author Administrator
 *
 */
public class DbUtils {

    //获得连接对象

    public static Connection getConnection(){
        Connection conn=null;
        try {
            Class.forName("oracle.jdbc.OracleDriver");
            conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "store", "store");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;

    }


    //执行增删改
    public static void cudData(String sql){
        Connection conn=getConnection();
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
            ps.executeUpdate();
        } catch (Exception e) {

            e.printStackTrace();
        }
    }

    //执行查询操作或者返回结果集
     public static ResultSet getResultData(String sql){
         Connection conn=getConnection();
         PreparedStatement ps=null;
         ResultSet rs=null;
            try {
                ps=conn.prepareStatement(sql);
                rs=ps.executeQuery();
            } catch (Exception e) {

                e.printStackTrace();
            }
            return rs;
     }

    //关闭连接释放资源

     public static void closeConnection(Connection conn){
         try {
             if(conn!=null){
                 conn.close();
             }
        } catch (Exception e) {
            e.printStackTrace();
        }

     }

}

有一个地方需要特别注意
在一个表中一个数据列的值往往对应的是另外一个对象这种情况下就需要new 出一个新对象来添加所需要的列表的值然后添加到需要返回值的类型中去

其实我就是想说比如两个表一个雇员一个雇主
在雇员中的ID 放在雇主中代表的其他的类型那么就需要讲其封装在雇主之中再塞入雇员中最后遍历出来

举个不是例子的例子其实我也是模模糊糊的感觉这样具体还要多敲代码才可以


while(rs.next()){
                product = new Product();

                product.setPid(rs.getInt(1));

                ProductType pt = new ProductType();
                pt.setProductTypeId(rs.getInt(2));

                product.setProductType(pt);

                product.setName(rs.getString(3));
            }