练习2

Dao.java

package com.bdqn.dao;

import java.util.List;

import com.bdqn.entity.Student;

public interface Dao {
    //增加
    int insert(Student stu);
    //删除
    int delete(Student stu);
    //修改
    int update(Student stu);
    //查询
    List<Student> seleteAll();
    //模糊查询
    List<Student> seleteName(Student stu);
}
... prompt'''

DaoBase.java

package com.bdqn.dao;

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

import com.bdqn.utils.ConfigManager;

public class DaoBase {


    //获取连接
    public static Connection getConnection(){   
        Connection conn = null;
        String driver = ConfigManager.getInstance().getValue("driver");
        String url = ConfigManager.getInstance().getValue("url");
        String user = ConfigManager.getInstance().getValue("user");
        String pwd = ConfigManager.getInstance().getValue("pwd");
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;        
    }

    //关闭资源
    public void close(Connection conn, Statement sta, ResultSet rs){
        try {
            if(conn!=null){
                conn.close();
            }
            if(sta!=null){
                sta.close();
            }
            if(rs!=null){
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //更新数据
    public int execute(String sql, Object[] obj){
        int num=0;
        Connection conn = getConnection();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);

            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i+1, obj[i]);          
            }
            num = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, ps, null);
        }
        return num;
    }

    //查询数据
    public ResultSet selete(String sql){
        ResultSet rs = null;
        Connection conn = getConnection();
        try {
            rs = conn.createStatement().executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }   

        return rs;      
    }


}

... prompt'''

Impl.java

package com.bdqn.dao.impl;

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

import com.bdqn.dao.Dao;
import com.bdqn.dao.DaoBase;
import com.bdqn.entity.Student;

public class Impl extends DaoBase implements Dao{

    //添加数据
    public int insert(Student stu) {
        String sql = "insert into student values(?,?,?,?,?)";
        Object[] obj = {stu.getSid(),stu.getSname(),stu.getSage(),stu.getShobby(),stu.getGid()};
        int num = super.execute(sql, obj);
        return num;
    }
    //删除
    public int delete(Student stu) {
        String sql = "delete from student where sid = ?";
        Object[] obj = {stu.getSid()};
        int num = super.execute(sql, obj);
        return num;
    }
    //更新
    public int update(Student stu) {
        String sql = "update student set sname=? where sid=?";
        Object[] obj = {stu.getSname(),stu.getSid()};
        int num = super.execute(sql, obj);
        return num;
    }
    //查询
    public List<Student> seleteAll() {
        ArrayList<Student> list = new ArrayList<Student>(); 
        String sql = "select *from student";
        ResultSet rs = super.selete(sql);
        try {
            while(rs.next()){
                Student stu = new Student();
                stu.setSid(rs.getInt("sid"));
                stu.setSname(rs.getString("sname"));
                stu.setSage(rs.getInt("sage"));
                stu.setShobby(rs.getString("shobby"));
                stu.setGid(rs.getInt("gid"));
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Student> seleteName() {
        ArrayList<Student> list = new ArrayList<Student>(); 
        String  sql = "selete *from student where sname like '%2%'";
        ResultSet rs = super.selete(sql);
        try {
            while(rs.next()){
                Student stu = new Student();
                stu.setSid(rs.getInt("sid"));
                stu.setSname(rs.getString("sname"));
                stu.setSage(rs.getInt("sage"));
                stu.setShobby(rs.getString("shobby"));
                stu.setGid(rs.getInt("gid"));
                list.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;

    }
    public List<Student> seleteName(Student stu) {
        return null;
    }




}

... prompt'''

Grade.java

package com.bdqn.entity;

public class Grade {
private int gid;
private String gname;
public int getGid() {
    return gid;
}
public void setGid(int gid) {
    this.gid = gid;
}
public String getGname() {
    return gname;
}
public void setGname(String gname) {
    this.gname = gname;
}
public Grade(int gid, String gname) {
    super();
    this.gid = gid;
    this.gname = gname;
}
public Grade() {
}
@Override
public String toString() {
    return "Grade [gid=" + gid + ", gname=" + gname + "]";
}



}

... prompt'''

Student.java

package com.bdqn.entity;

public class Student {
private int sid;
private String sname;
private int sage;
private String shobby;
private int gid;
public int getSid() {
    return sid;
}
public void setSid(int sid) {
    this.sid = sid;
}
public String getSname() {
    return sname;
}
public void setSname(String sname) {
    this.sname = sname;
}
public int getSage() {
    return sage;
}
public void setSage(int sage) {
    this.sage = sage;
}
public String getShobby() {
    return shobby;
}
public void setShobby(String shobby) {
    this.shobby = shobby;
}
public int getGid() {
    return gid;
}
public void setGid(int gid) {
    this.gid = gid;
}

public Student(int sid, String sname, int sage, String shobby, int gid) {
    this.sid = sid;
    this.sname = sname;
    this.sage = sage;
    this.shobby = shobby;
    this.gid = gid;
}

public Student() {

}
@Override
public String toString() {
    return "Student [sid=" + sid + ", sname=" + sname + ", sage=" + sage
            + ", shobby=" + shobby + ", gid=" + gid + "]";
}







}

... prompt'''

ConfigManager.java

package com.bdqn.utils;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public  class ConfigManager {
    private static Properties prop;
    private static ConfigManager config;
    InputStream is;

    //私有构造
    private ConfigManager(){
        prop = new Properties();
        try {
            is = ConfigManager.class.getClassLoader().getResourceAsStream("database.properties");
            prop.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if(is!=null){
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    //获得ConfigManager对象
    public static synchronized ConfigManager getInstance(){
        if(config==null){
        synchronized (ConfigManager.class) {
            if(config==null){
                config = new ConfigManager();
            }           
        }
        }
        return config;
    }

    //获取配置文件的值
    public String getValue(String key){
        String value = prop.getProperty(key);
        return value;

    }
}

... prompt'''

Test.java

package com.bdqn.entity;

import java.util.List;

import com.bdqn.dao.impl.Impl;

public class Test {
    public static void main(String[] args) {
        Impl impl =new Impl();
        int num =0;



        /*   impl.insert(new Student(1, "01", 20, "01", 1));
        impl.insert(new Student(2, "02", 20, "02", 1));
        impl.insert(new Student(3, "03", 20, "03", 2));*/

        List<Student> list = impl.seleteAll();
        for (int i = 0; i < list.size(); i++) {         
            System.out.println(list.get(i).toString());
        }

        Student stu1 = new Student();
        stu1.setSid(1);
        num = impl.delete(stu1);
        System.out.println(num);

        Student stu2 = new Student();
        stu2.setSname("修改名字");
        stu2.setSid(2);
        System.out.println(impl.update(stu2));

        List<Student> list2 = impl.seleteName();
        for (int i = 0; i < list2.size(); i++) {            
            System.out.println(list.get(i).toString());
        }

    }
}
... prompt'''

database.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/student
user=root
pwd=root
... prompt'''