【Java】 *** 作数据库_非关系型数据库

概述工具： eclipse MySQL Navicat for MySQL MySQL 连接驱动：mysql-connector-java-5.0.4-bin.jar SQL 代码 CREATE TABL

工具： eclipse
　　　 MysqL
　　　 Navicat for MysqL
　　　 MysqL 连接驱动：mysql-connector-java-5.0.4-bin.jar

sql 代码

CREATE table user(    ID          VARCHAR(255),name          VARCHAR(255),birth        VARCHAR(255),gender      VARCHAR(255),favorite      VARCHAR(255));

Java 代码

package model.vo;public class Student {    private String ID;    private String name;    private String birth;    private String gender;    private String favorite;        public Student(String ID,String name,String birth,String gender,String favorite) {        super();        this.ID = ID;        this.name = name;        this.birth = birth;        this.gender = gender;        this.favorite = favorite;    }    public Student() {        super();    }    public String getID() {        return ID;    }    public voID setID(String ID) {        this.ID = ID;    }    public String getname() {        return name;    }    public voID setname(String name) {        this.name = name;    }    public String getBirth() {        return birth;    }    public voID setBirth(String birth) {        this.birth = birth;    }    public String getFavorite() {        return favorite;    }    public voID setFavorite(String favorite) {        this.favorite = favorite;    }    public String getGender() {        return gender;    }    public voID setGender(String gender) {        this.gender = gender;    }}

Java 连接所需的jar包下载

百度网盘提取码: 68pv

使用方法：
　　1. 在工程目录中创建lib文件夹，将下载好的JDBC（jar包）放到该文件夹下。
　　2. 右键工程名，选择 BuIDing Path | Configure BuIDing Path ，在java build path中的librarIEs分页中选择Add JARs...，选择刚才添加的JDBC（jar包）。

数据库连接

package tools;import java.sql.Connection;import java.sql.DriverManager;import java.sql.sqlException;public class DbTools {    public Connection con;    public Connection getCon() throws Exception {        // 1.加载驱动(按照驱动类的名字《完整名字：包名.类名》把该类加载到内存中)        Class.forname("com.MysqL.jdbc.Driver");        // 2.建立连接        String url = "jdbc:MysqL://localhost:3306/work?useUnicode=true&characterEnCoding=utf-8";        String dbUser = "root";        String dbPassword = "MysqLadmin";        con = DriverManager.getConnection(url,dbUser,dbPassword);        System.out.println("success");        return con;    }        public voID close() throws Exception{        if(con!=null)            con.close();    }    }

增

    public voID add(String ID,String favorite) throws Exception    {        DbTools db = new DbTools();        Connection con = db.getCon();        // 创建语句        String sql = "insert into tstudent (ID,name,gender,birth,favorite) values (?,?,?) ";        PreparedStatement pst = con.prepareStatement(sql);        pst.setString(1,ID);        pst.setString(2,name);        pst.setString(3,gender);        pst.setString(4,birth);        pst.setString(5,favorite);        pst.executeUpdate();    }

删

    public voID delete(String ID) throws Exception{        DbTools db=new DbTools();        Connection con=db.getCon();        // 3.创建语句        String sql = " delete from tstudent where ID=? ";        PreparedStatement pst = con.prepareStatement(sql);        pst.setString(1,ID);        pst.executeUpdate();        pst.close();        db.close();    }

改

    public voID updateStudent(String ID,String favorite) throws Exception    {        DbTools db = new DbTools();        Connection con = db.getCon();        // 创建语句        String sql = "update tstudent set ";        if(name!=null && name.length()>0)            sql=sql+" and name = ?";        if(gender!=null && gender.length()>0)            sql=sql+" and gender=?";        if(birth!=null && birth.length()>0)            sql+= " and birth=?";        if(birth!=null && favorite.length()>0)            sql+= " and favorite=?";        sql+=" where ID='?' ";                System.out.println(sql);                PreparedStatement pst = con.prepareStatement(sql);        // 4.执行语句(语句中如果有参数，则需要先对参数赋值)        ArrayList<String> params=new ArrayList<String>();        if(ID!=null && ID.length()>0)            params.add(ID);        if(name!=null && name.length()>0)            params.add(name);        if(gender!=null && gender.length()>0)            params.add(gender);        for(int i=0;i<params.size();i++){            pst.setString(i+1,params.get(i));        }                pst.executeUpdate();        pst.close();        db.close();    }

查

    // 按条件查询满足条件的学生数据    /*     * 参数：查询条件 返回：查询结果（若干个，使用集合类型<>）     */    public ArrayList<Student> query(String ID,String gender) throws Exception {        ArrayList<Student> result = new ArrayList<Student>();            DbTools db=new DbTools();        Connection con=db.getCon();        // 3.创建语句        String sql = " select * from tstudent where 1=1 ";        if(ID!=null && ID.length()>0)            sql=sql+" and ID=? ";        if(name!=null && name.length()>0)            sql=sql+" and name = ?";        if(gender!=null && gender.length()>0)            sql=sql+" and gender=?";        System.out.println(sql);                PreparedStatement pst = con.prepareStatement(sql);        // 4.执行语句(语句中如果有参数，则需要先对参数赋值)        ArrayList<String> params=new ArrayList<String>();        if(ID!=null && ID.length()>0)            params.add(ID);        if(name!=null && name.length()>0)            params.add(name);        if(gender!=null && gender.length()>0)            params.add(gender);        for(int i=0;i<params.size();i++){            pst.setString(i+1,params.get(i));        }                        ResultSet rs = pst.executequery();  //执行查询        while(rs.next()){            Student student=new Student();            student.setID(rs.getString("ID"));            student.setname(rs.getString("name"));            student.setGender(rs.getString("gender"));            student.setBirth(rs.getString("birth"));            student.setFavorite(rs.getString("favorite"));            result.add(student);        }                rs.close();        pst.close();        db.close();        return result;    }

提示