工具: eclipse
@H_403_6@
MysqL
Navicat for MysqL
MysqL 连接驱动:MysqL-connector-java-5.0.4-bin.jarsql 代码
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包下载
使用方法:
@H_403_6@
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; }
提示
可保存这此Java项目,下次连接可直接导入,只需修改部分信息。
@H_403_6@