利用JDBC对数据库进行简单的操作

通过JDBC对数据库进行简单的操作

bean类

package com.bdqn.entity;

import java.io.Serializable;

public class Student implements Serializable{
    private int sid;
    private String sname;
    private int sage;
    private String sdesc;

    public Student() {
    }

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



    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 getSdesc() {
        return sdesc;
    }

    public void setSdesc(String sdesc) {
        this.sdesc = sdesc;
    }

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



}

baseDao类

package com.bdqn.dao1;

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 java.util.List;

import com.bdqn.entity.Student;
import com.bdqn.utils.JdbcUtils;

public class BaseDao {
    private static Connection conn=null;
    //获取连接
    public static Connection getConnection(){
        try {
            Class.forName(JdbcUtils.driver);
            conn=DriverManager.getConnection(JdbcUtils.url, JdbcUtils.user, JdbcUtils.pwd);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return conn;
    }
    //关闭资源
    public static void close(Connection conn,Statement sta,ResultSet rs){
        try {
            if (rs!=null) {
                rs.close();
            }
            if (sta!=null) {
                sta.close();
            }
            if (conn!=null) {
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    //增删改
    public static int update(String sql,Object [] objects){
        int num = 0;
        PreparedStatement ps=null;
        try {
            conn=getConnection();
            ps=conn.prepareStatement(sql);
            if (objects!=null && objects.length>0) {
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject((i+1), objects[i]);
                }
            }
            num=ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return num;

    }
    //查询
    public static ResultSet getResultSet(String sql,Object [] objects){
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=getConnection();
            ps=conn.prepareStatement(sql);
            if (objects!=null && objects.length>0) {
                for (int i = 0; i < objects.length; i++) {
                    ps.setObject((i+1), objects[i]);
                }
            }
            rs=ps.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }

}

dao类

package com.bdqn.dao1;

import java.util.List;

import com.bdqn.entity.Student;

public interface StudentDao {
    public int insert(Student stu);//增加
    public int delete(int sid);//删除
    public int update(Student stu);//修改
    public List<Student> selectAllStudents();//全部查询
    public List<Student> selectStudents(int sid);//单个查询
}

daoimpl类

package com.bdqn.dao.impl;

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

import com.bdqn.dao1.BaseDao;
import com.bdqn.dao1.StudentDao;
import com.bdqn.entity.Student;

public class StudentImpl extends BaseDao implements StudentDao{

//增
    @Override
    public int insert(Student stu) {
        String insertSql="insert into student values(?,?,?,?)";
        Object [] objects={stu.getSid(),stu.getSname(),stu.getSage(),stu.getSdesc()};
        int num=this.update(insertSql, objects);
        return num;
    }
//删
    @Override
    public int delete(int sid) {
        String deleteSql="delete from student where sid=?";
        Object [] objects={sid};
        int num=this.update(deleteSql, objects);
        return num;
    }
//改
    @Override
    public int update(Student stu) {
        String updateSql="update student set sname=?";
        Object [] objects={stu.getSname()};
        int num=this.update(updateSql, objects);
        return num;
    }
//全查询
    @Override
    public List<Student> selectAllStudents() {
        String sql = "select * from student"; 
        List<Student> list=null;
        try {
            ResultSet rs=this.getResultSet(sql, null);
            list=new ArrayList<Student>();
            while (rs.next()) {
                Student stu=new Student();

                int id=rs.getInt("sid");
                String name=rs.getString("sname");
                int age=rs.getInt("sage");
                String desc=rs.getString("sdesc");

                stu.setSid(id);
                stu.setSname(name);
                stu.setSage(age);
                stu.setSdesc(desc);

                list.add(stu);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }
//根据ID进行查询
    @Override
    public List<Student> selectStudents(int sid) {
        String sql = "select * from student where sid=?";
        List<Student> list=null;
        try {
            ResultSet rs=this.getResultSet(sql, null);
            list=new ArrayList<Student>();
            while (rs.next()) {
                Student stu=new Student();

                int id=rs.getInt("sid");
                String name=rs.getString("sname");
                int age=rs.getInt("sage");
                String desc=rs.getString("sdesc");

                stu.setSid(id);
                stu.setSname(name);
                stu.setSage(age);
                stu.setSdesc(desc);

                list.add(stu);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }


}

util类

package com.bdqn.utils;

public class JdbcUtils {
    public static final String driver="com.mysql.jdbc.Driver";
    public static final String url="jdbc:mysql://localhost:3306/student";
    public static final String user="root";
    public static final String pwd="root";



}

测试类

package com.bdqn.test;

import java.util.List;

import com.bdqn.dao.impl.StudentImpl;
import com.bdqn.dao1.StudentDao;
import com.bdqn.entity.Student;

public class Test {
    public static void main(String[] args) {
        StudentDao dao=new StudentImpl();
        /**
         * 增加
         */
        /*Student stu=new Student();
        int num=dao.insert(stu);
        System.out.println(num);*/
        /**
         * 删除（根据sid）
         */
        /*Student stu=new Student();
        dao.delete(0);*/
        /**
         * 修改名字
         */
        /*Student stu=new Student();
        int num=dao.update(stu);
        System.out.println(num);*/
        /**
         * 全查
         */
        List<Student> list=dao.selectAllStudents();
        for(Student stu : list){
            System.out.println(stu.toString());
        }
    }

}

脚注

生成一个脚注1.

目录

用 [TOC]来生成目录：

---

1. 这里是 脚注 的 内容. ↩