JDBC

1.BaseDao.java

package dao;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import utils.ConfigManager;//基类 写好连接数据库 增删改查和释放资源就不用改了public class BaseDao {    ResultSet rs = null;    PreparedStatement ps = null;    Connection connection = null;    public boolean getConnection(){            try {                Class.forName(ConfigManager.getInstance().getString("driver"));                String url=ConfigManager.getInstance().getString("url");                String user=ConfigManager.getInstance().getString("user");                String password=ConfigManager.getInstance().getString("password");                connection=DriverManager.getConnection(url, user, password);                System.out.println("连接成功");            } catch (ClassNotFoundException e) {                // TODO Auto-generated catch block                e.printStackTrace();                return false;            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();                return false;            }        return true;    }    //增删改    public int upDate(String sql,Object[]params){        int update=0;        if(this.getConnection()){            try {                ps=connection.prepareStatement(sql);                for(int i=0;i<params.length;i++){                    ps.setObject(i+1, params[i]);                }                update=ps.executeUpdate();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        return update;    }    //查    public ResultSet query(String sql,Object[]params){        if(this.getConnection()){            try {                ps=connection.prepareStatement(sql);                for(int i=0;i<params.length;i++){                    ps.setObject(i+1, params[i]);                }                rs=ps.executeQuery();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        return rs;    }    //释放资源    public static void close(Connection connection,PreparedStatement ps,ResultSet rs){        if(rs!=null){            try {                rs.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        if(ps!=null){            try {                ps.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        if(connection!=null){            try {                connection.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }}

2.Dao.java

package dao;import entity.Dept;public interface Dao {    // 增加数据方法    public void add(Dept dept);    // 删除数据    public void delete(Dept dept);    // 改变数据    public void up(Dept dept);    // 查询数据    public void allSelect();    // 模糊查询    public void like(String sname);    //子查询    public void ziLike(int gid);}

3.impl.java

package impl;import java.sql.ResultSet;import java.sql.SQLException;import dao.BaseDao;import dao.Dao;import entity.Dept;//这里就是专门写SQL语句的public class Impl extends BaseDao implements Dao {    //添加数据    public void add(Dept dept){        String sql="INSERT INTO student VALUES (?,?,?,?,?)";        Object[]params={dept.getSid(),dept.getSname(),dept.getSage(),dept.getShobby(),dept.getGid()};        int i=this.upDate(sql, params);        if(i>0){            System.out.println("插入成功");        }else{            System.out.println("插入失败");        }    }    //删除数据    public void delete(Dept dept){        String sql="delete from student where sid=?";        Object[]params={dept.getSid()};        int i=this.upDate(sql, params);        if(i>0){            System.out.println("删除成功");        }else{            System.out.println("删除失败");        }    }    //改变数据    public void up(Dept dept){        String sql="update student set sname=? where sid=?";        Object[]params={dept.getSname(),dept.getSid()};        int i=this.upDate(sql, params);        if(i>0){            System.out.println("改变成功");        }else{            System.out.println("改变失败");        }    }    //查询数据    public void allSelect(){        String sql="select *from student";        Object[]params={};        ResultSet rs=this.query(sql, params);        try {            while(rs.next()){                int sid = rs.getInt("sid");                String  sname=rs.getString("sname");                int sage = rs.getInt("sage");                String shobby = rs.getString("shobby");                int gid = rs.getInt("gid");                System.out.println(sid + "\t"+sname+"\t" + sage+"\t"+shobby+"\t"+gid);            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    //模糊查询    public void like(String sname){        String sql="SELECT *FROM student WHERE sname LIKE  \"%\"?\"%\" ";        Object[]params={sname};        ResultSet rs=this.query(sql, params);        try {            while(rs.next()){                int sid = rs.getInt("sid");                String  dname=rs.getString("sname");                int sage = rs.getInt("sage");                String shobby = rs.getString("shobby");                int gid = rs.getInt("gid");                System.out.println(sid + "\t"+dname+"\t" + sage+"\t"+shobby+"\t"+gid);            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    //子查询    public void ziLike(int gid){        String sql="SELECT  *FROM student WHERE gid IN(SELECT gid FROM grade r WHERE r.`gid`=? )";        Object[]params={gid};        ResultSet rs=this.query(sql, params);        try {            while(rs.next()){                int sid = rs.getInt("sid");                String  dname=rs.getString("sname");                int sage = rs.getInt("sage");                String shobby = rs.getString("shobby");                int aid = rs.getInt("gid");                System.out.println(sid + "\t"+dname+"\t" + sage+"\t"+shobby+"\t"+aid);            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }}

4.entity包

package entity;public class Dept {    private int sid;    private String sname;    private int sage;    private String shobby;    private int gid;    /**     * @return the sid     */    public int getSid() {        return sid;    }    /**     * @param sid the sid to set     */    public void setSid(int sid) {        this.sid = sid;    }    /**     * @return the sname     */    public String getSname() {        return sname;    }    /**     * @param sname the sname to set     */    public void setSname(String sname) {        this.sname = sname;    }    /**     * @return the sage     */    public int getSage() {        return sage;    }    /**     * @param sage the sage to set     */    public void setSage(int sage) {        this.sage = sage;    }    /**     * @return the shooby     */    public String getShobby() {        return shobby;    }    /**     * @param shooby the shooby to set     */    public void setShooby(String shobby) {        this.shobby = shobby;    }    /**     * @return the gid     */    public int getGid() {        return gid;    }    /**     * @param gid the gid to set     */    public void setGid(int gid) {        this.gid = gid;    }    /* (non-Javadoc)     * @see java.lang.Object#toString()     */    @Override    public String toString() {        return "Dept [sid=" + sid + ", sname=" + sname + ", sage=" + sage                + ", shooby=" + shobby + ", gid=" + gid + "]";    }}

Utils包

package utils;import java.io.IOException;import java.io.InputStream;import java.util.Properties;//读取数据库属性文件，获取数据库连接信息public class ConfigManager {    private  static ConfigManager configManager;    private Properties properties;    // 构造方法 方法名和类名一样    private ConfigManager() {        String configFile = "database.properties";        // 把configFile通过ConfigManager类加载器的.getResourceAsStream方法读到输入流        InputStream in = ConfigManager.class.getClassLoader().getResourceAsStream(configFile);        // IN通过properties.load方法load到properties对象里面去;        properties=new Properties();        try {            properties.load(in);        } catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } finally {            try {                if (in != null) {                    in.close();                }            } catch (IOException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }    //提供给别人一个唯一的ConfigManager对象    public static synchronized ConfigManager getInstance(){        if(configManager==null){            synchronized (ConfigManager.class){                if(configManager==null){                    configManager=new ConfigManager();                }            }        }        return configManager;    }    // 根据属性文件中的键获得对应的值    public String getString(String key) {        return properties.getProperty(key);    }}