java中的基本jdbc中mvc基本示例

数据库:

包文件:

Student.java

package com.model;

public class Student {
    private int id;
    private String name;
    private int age;
    private String address;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

DBUtil.java

package com.db;

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

public class DBUtil {
    private static final String URL="jdbc:mysql://127.0.0.1:3306/jdbc";
    private static final String USER="root";
    private static final String PASSWORD="gys";
    private static Connection conn=null;
    static{
        try {
            //加载驱动程序
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接
            conn= DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection(){
        return conn;
    }
    
    public static void main(String[] args) throws Exception{
        
        //创建statement对象
        Statement stmt=conn.createStatement();
        ResultSet rs=stmt.executeQuery("select * from student");
         
        while(rs.next()){
            System.out.println(rs.getInt("id")+","+rs.getString("name")+","+rs.getInt("age")+","+rs.getString("address"));
        }
        
    }
}

StudentDao.java

package com.dao;

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

import com.db.DBUtil;
import com.model.Student;

/*
 *
 */
public class StudentDao {
    // 添加
    public void addStudent(Student s) throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = "insert into student(name,age,address) values(?,?,?)";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1, s.getName());
        ptmt.setInt(2, s.getAge());
        ptmt.setString(3, s.getAddress());
        ptmt.execute();
    }

    // 更新
    public void updateStudent(Student s) throws Exception {
        Connection conn = DBUtil.getConnection();
        String sql = " update student set name=?,age=?,address=? where id=? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1, s.getName());
        ptmt.setInt(2, s.getAge());
        ptmt.setString(3, s.getAddress());
        ptmt.setInt(4, s.getId());
        ptmt.execute();
        System.out.println("更新完毕.");

    }

    // 删除
    public void delStudent(Integer id) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = " delete from student where id=? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setInt(1, id);
        ptmt.execute();
        System.out.println("删除成功.");
    }

    // 查询list
    public List<Student> query() throws Exception {
        Connection conn = DBUtil.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select * from student");
        List<Student> list = new ArrayList<Student>();
        Student student = null;
        while (rs.next()) {
            student = new Student();
            student.setId(rs.getInt("id"));
            student.setName(rs.getString("name"));
            student.setAge(rs.getInt("age"));
            student.setAddress(rs.getString("address"));
            list.add(student);
        }
        return list;
    }

    // 查询list
    public List<Student> query(String name) throws Exception {
        Connection conn = DBUtil.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append("select * from student ");
        sb.append(" where name=?");
        PreparedStatement ptmt = conn.prepareStatement(sb.toString());
        ptmt.setString(1, name);
        ResultSet rs = ptmt.executeQuery();
        List<Student> list = new ArrayList<Student>();
        Student student = null;
        while (rs.next()) {
            student = new Student();
            student.setId(rs.getInt("id"));
            student.setName(rs.getString("name"));
            student.setAge(rs.getInt("age"));
            student.setAddress(rs.getString("address"));
            list.add(student);
        }
        return list;
    }

    // 查询list
    public List<Student> query(List<Map<String, Object>> params)
            throws Exception {
        Connection conn = DBUtil.getConnection();
        StringBuilder sb = new StringBuilder();
        sb.append("select * from student where 1=1 ");
        //sb.append(" where name=?");
        if(params !=null&&params.size()>0){
            for(int i=0;i<params.size();i++){
                Map<String,Object> map=params.get(i);
                sb.append(" and "+map.get("name")+" "+map.get("rela")+" "+map.get("value"));
            }
        }
        PreparedStatement ptmt = conn.prepareStatement(sb.toString());
        //ptmt.setString(1, name);
        ResultSet rs = ptmt.executeQuery();
        List<Student> list = new ArrayList<Student>();
        Student student = null;
        while (rs.next()) {
            student = new Student();
            student.setId(rs.getInt("id"));
            student.setName(rs.getString("name"));
            student.setAge(rs.getInt("age"));
            student.setAddress(rs.getString("address"));
            list.add(student);
        }
        return list;
    }

    // 查询单个
    public Student getStudent(Integer id) throws SQLException {
        Connection conn = DBUtil.getConnection();
        String sql = " select * from student where id=? ";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setInt(1, id);
        // Statement stmt=conn.createStatement();
        ResultSet rs = ptmt.executeQuery();
        Student student = null;
        while (rs.next()) {
            student = new Student();
            student.setId(rs.getInt("id"));
            student.setName(rs.getString("name"));
            student.setAge(rs.getInt("age"));
            student.setAddress(rs.getString("address"));
        }
        return student;
    }
}

StudentAction.java

package com.action;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.dao.StudentDao;
import com.model.Student;

/*
 * 控制层
 */
public class StudentAction {
    public void add(Student student) throws Exception{
        StudentDao dao=new StudentDao();
        dao.addStudent(student);
    }
    public void edit(Student student) throws Exception{
        StudentDao dao=new StudentDao();
        dao.updateStudent(student);
    }
    public Student getStudent(Integer id) throws SQLException{
        StudentDao dao=new StudentDao();
        return dao.getStudent(id);
    }
    public void del(Integer id) throws SQLException{
        StudentDao dao=new StudentDao();
        dao.delStudent(id);
    }
    public List<Student> query() throws Exception{
        StudentDao dao=new StudentDao();
        return dao.query();
    }
    public List<Student> query(List<Map<String,Object>> params) throws Exception{
        StudentDao dao=new StudentDao();
        return dao.query(params);
    }    
    //public static void main(String[] args) throws Exception {
        //StudentDao studentDao=new StudentDao();
        
        //添加学生
        /*Student student=new Student();
        student.setName("嘎嘎");
        student.setAge(26);
        student.setAddress("安徽省");
        studentDao.addStudent(student);*/
        
        //查询学生list
        /*List<Student> list=studentDao.query();
        for(Student s:list){
            System.out.println(s.getId()+","+s.getName()+","+s.getAge()+","+s.getAddress());
        }*/
        
        /*List<Student> list=studentDao.query("嘎嘎");
        for(Student s:list){
            System.out.println(s.getId()+","+s.getName()+","+s.getAge()+","+s.getAddress());
        }*/
        
    /*    List<Map<String,Object>>params=new ArrayList<Map<String,Object>>();
        Map<String,Object> param=new HashMap<String,Object>();
        param.put("name","name");
        //param.put("rela","=");
        param.put("rela"," like ");
        param.put("value","'嘎嘎'");
        params.add(param);
        
        List<Student> list=studentDao.query(params);
        for(Student s:list){
            System.out.println(s.getId()+","+s.getName()+","+s.getAge()+","+s.getAddress());
        }*/
        //更新数据
        /*Student student=new Student();
        student.setId(3);
        student.setName("hee");
        student.setAge(100);
        student.setAddress("月球上");
        studentDao.updateStudent(student);*/
        
        //查询单个学生
//        Student student=studentDao.getStudent(2);
//        System.out.println(student.getName()+";"+student.getAge()+","+student.getAddress());
        
        //删除
        //studentDao.delStudent(3);
    //}
}