------<a href="http://www.itheima.com" target="blank">Java培训、Android培训、iOS培训、.Net培训</a>、期待与您交流! -------
通过今天对Java如何操作MySql数据库的学习,以及查阅API,大致了解了如何通过JDBC去操作数据库。具体实现了对数据库的增删改查。直接上代码:
package database_operate;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
*
* 数据库帮助类
*
* 具体实现,数据库连接,创建数据表,增删改查
*
* @author VisionDo
*
*/
public class DBHelper{
<span style="white-space:pre"> </span>private static final String DRIVER = "com.mysql.jdbc.Driver";
<span style="white-space:pre"> </span>private static final String URL = "jdbc:mysql://localhost:3306/wireless_db";
<span style="white-space:pre"> </span>private static final String USER = "Vision";
<span style="white-space:pre"> </span>private static final String PASSWORD = "**..123";
<span style="white-space:pre"> </span>private static final String ENCODEING = "useUnicode=true&characterEncoding=utf-8";
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>private static final String TABLE_NAME = "studentInf";
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>private DriverManager DM;
<span style="white-space:pre"> </span>private Connection cnn;
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>public DBHelper( ){
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>cnn = DM.getConnection(URL, USER, PASSWORD);
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("无法连接数据库!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>/**
<span style="white-space:pre"> </span> * 创建数据表
<span style="white-space:pre"> </span> * 根据传来的参数,使用反射机制,获取类内的成员,建立数据表
<span style="white-space:pre"> </span> *
<span style="white-space:pre"> </span> * @param o
<span style="white-space:pre"> </span> */
<span style="white-space:pre"> </span>public void CreateTable(Object o){
<span style="white-space:pre"> </span>Class<?> c = o.getClass();
<span style="white-space:pre"> </span>Field[] field = c.getDeclaredFields();
<span style="white-space:pre"> </span>StringBuffer sb = new StringBuffer();
<span style="white-space:pre"> </span>String s = "create table "+TABLE_NAME+"( No varchar(40) primary key , ";
<span style="white-space:pre"> </span>sb.append(s);
<span style="white-space:pre"> </span>for(int i = 0; i< field.length; i++){
<span style="white-space:pre"> </span>String ConcluName = field[i].getName();
<span style="white-space:pre"> </span>if(!ConcluName.equals("No")){
<span style="white-space:pre"> </span>String Type = field[i].getType().getSimpleName();
<span style="white-space:pre"> </span>if(i+1 == field.length)
<span style="white-space:pre"> </span>sb.append(ConcluName+" "+Type);
<span style="white-space:pre"> </span>else
<span style="white-space:pre"> </span>sb.append(ConcluName+" "+Type+", ");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>sb.append(");");
<span style="white-space:pre"> </span>String sql = sb.toString().replaceAll("String", "varchar(16)");
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>PreparedStatement pre = cnn.prepareStatement(sql);
<span style="white-space:pre"> </span>boolean IsDid = pre.execute();
<span style="white-space:pre"> </span>if(!IsDid)
<span style="white-space:pre"> </span>System.out.println("学生信息表创建成功!");
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("学生信息表创建失败!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>//添加数据
<span style="white-space:pre"> </span>public void AddOper(Object o){
<span style="white-space:pre"> </span>String sql = "insert into "+TABLE_NAME
<span style="white-space:pre"> </span>+ "(No,"
<span style="white-space:pre"> </span>+ "Name,"
<span style="white-space:pre"> </span>+ "Sex,"
<span style="white-space:pre"> </span>+ "Garden,"
<span style="white-space:pre"> </span>+ "Math,"
<span style="white-space:pre"> </span>+ "English,"
<span style="white-space:pre"> </span>+ "Chinese,"
<span style="white-space:pre"> </span>+ "TotalScore)value(?,?,?,?,?,?,?,?);";
<span style="white-space:pre"> </span>Student s = (Student) o;
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>PreparedStatement pre = cnn.prepareStatement(sql);
<span style="white-space:pre"> </span>pre.setString(1, s.getNo());
<span style="white-space:pre"> </span>pre.setString(2, s.getName());
<span style="white-space:pre"> </span>pre.setString(3, s.getSex());
<span style="white-space:pre"> </span>pre.setString(4, s.getGarden());
<span style="white-space:pre"> </span>pre.setDouble(5, s.getMath());
<span style="white-space:pre"> </span>pre.setDouble(6, s.getEnglish());
<span style="white-space:pre"> </span>pre.setDouble(7, s.getChinese());
<span style="white-space:pre"> </span>double TotalScore = Double.valueOf(s.getChinese()+s.getEnglish()+s.getMath());
<span style="white-space:pre"> </span>pre.setDouble(8, TotalScore);
<span style="white-space:pre"> </span>boolean IsDid = pre.execute();
<span style="white-space:pre"> </span>if(!IsDid)
<span style="white-space:pre"> </span>System.out.println("学生 "+s.getName()+" 的信息已经添加到数据库中!");
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("添加学生操作失败!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>//删除数据
<span style="white-space:pre"> </span>public void DeleteOper(String No){
<span style="white-space:pre"> </span>String sql = "delete from "+TABLE_NAME+" where No = ?";
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>PreparedStatement pre = cnn.prepareStatement(sql);
<span style="white-space:pre"> </span>pre.setString(1, No);
<span style="white-space:pre"> </span>boolean IsDid = pre.execute();
<span style="white-space:pre"> </span>if(!IsDid){
<span style="white-space:pre"> </span>System.out.println("学号为:"+No+" 的学生信息已删除!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("删除学生信息失败!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>//更新数据
<span style="white-space:pre"> </span>public void UpdateOper(String No,String StrName,Object NewValue){
<span style="white-space:pre"> </span>String sql = "update "+TABLE_NAME+" set "+StrName+" ="+NewValue+" where No=?";
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>PreparedStatement pre = cnn.prepareStatement(sql);
<span style="white-space:pre"> </span>pre.setString(1, No);
<span style="white-space:pre"> </span>boolean IsDid = pre.execute();
<span style="white-space:pre"> </span>if(!IsDid){
<span style="white-space:pre"> </span>System.out.println("学号为:"+No+" 的学生 "+StrName+" 的信息已修改!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("修改学生信息失败!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>//查询所有信息
<span style="white-space:pre"> </span>public void SearchOper(String No){
<span style="white-space:pre"> </span>String sql = "select * from "+TABLE_NAME+" where No=?";
<span style="white-space:pre"> </span>PreparedStatement pre;
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>pre = cnn.prepareStatement(sql);
<span style="white-space:pre"> </span>pre.setString(1, No);
<span style="white-space:pre"> </span>ResultSet rs = pre.executeQuery();
<span style="white-space:pre"> </span>StringBuffer sb = new StringBuffer();
<span style="white-space:pre"> </span>Field[] fields = Student.class.getDeclaredFields();
<span style="white-space:pre"> </span>int i = 0;
<span style="white-space:pre"> </span>String str;
<span style="white-space:pre"> </span>while(rs.next()){
<span style="white-space:pre"> </span>while(i<8){
<span style="white-space:pre"> </span>str = fields[i].getName()+" = ";
<span style="white-space:pre"> </span>sb.append(str);
<span style="white-space:pre"> </span>if(i<4)
<span style="white-space:pre"> </span>sb.append(rs.getString(fields[i].getName()));
<span style="white-space:pre"> </span>else
<span style="white-space:pre"> </span>sb.append(rs.getDouble(fields[i].getName()));
<span style="white-space:pre"> </span> sb.append("\n");
<span style="white-space:pre"> </span> i++;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>System.out.println("学号为:"+No+" 的学生是\n"+sb.toString());
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("查找失败!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>//查询单项信息
<span style="white-space:pre"> </span>public void SearchOper(String No,String inf){
<span style="white-space:pre"> </span>String sql = "select "+inf+" from "+TABLE_NAME+" where No=?";
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>PreparedStatement pre;
<span style="white-space:pre"> </span>try {
<span style="white-space:pre"> </span>pre = cnn.prepareStatement(sql);
<span style="white-space:pre"> </span>pre.setString(1, No);
<span style="white-space:pre"> </span>ResultSet rs = pre.executeQuery();
<span style="white-space:pre"> </span>StringBuffer sb = new StringBuffer();
<span style="white-space:pre"> </span>Object type = null;
<span style="white-space:pre"> </span>Field[] fields = Student.class.getDeclaredFields();
<span style="white-space:pre"> </span>for(Field f:fields){
<span style="white-space:pre"> </span>if(f.getName().equals(inf)){
<span style="white-space:pre"> </span>type = Modifier.toString(f.getModifiers());
<span style="white-space:pre"> </span>break;
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>if(type != null){
<span style="white-space:pre"> </span>while(rs.next()){
<span style="white-space:pre"> </span>if(type instanceof Double)
<span style="white-space:pre"> </span>sb.append(rs.getDouble(inf));
<span style="white-space:pre"> </span>else
<span style="white-space:pre"> </span>sb.append(rs.getString(inf));
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>System.out.println("学号为:"+No+" 的学生 "+inf+"="+sb.toString());
<span style="white-space:pre"> </span>}else
<span style="white-space:pre"> </span>System.out.println("学生信息表中没有"+inf+"项!");
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>} catch (SQLException e) {
<span style="white-space:pre"> </span>System.out.println("查找失败!");
<span style="white-space:pre"> </span>}
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>
<span style="white-space:pre"> </span>}
}
主函数:
package database_operate;
/**
*
* 通过JDBC操作mySql数据库
* 实现增删改查
*
* @author VisionDo
*
*/
public class DataBase_Exp {
public static void main(String[] args) {
DBHelper dbHelper = new DBHelper();
Student s1 = new Student();
dbHelper.CreateTable(s1);//创建表
}
}
class Student{
private String No;
private String Name;
private String Sex;
private String Garden;
private double Math;
private double English;
private double Chinese;
private double TotalScore;
public String getName() {
return Name;
}
public void setName(String name) {
Name = name;
}
public String getGarden() {
return Garden;
}
public void setGarden(String garden) {
Garden = garden;
}
public double getMath() {
return Math;
}
public void setMath(double math) {
Math = math;
}
public double getEnglish() {
return English;
}
public void setEnglish(double english) {
English = english;
}
public double getChinese() {
return Chinese;
}
public void setChinese(double chinese) {
Chinese = chinese;
}
public double getTotalScore() {
return TotalScore;
}
public void setTotalScore(double totalScore) {
TotalScore = totalScore;
}
public String getSex() {
return Sex;
}
public void setSex(String sex) {
Sex = sex;
}
public String getNo() {
return No;
}
public void setNo(String no) {
No = no;
}
}
执行后:
可以看到数据库已经成功创建了
在数据库管理工具中,也可以看到建立的数据表
接下来向数据库中插入数据:
s1.setNo("20150001");
s1.setName("李雅");
s1.setSex("女");
s1.setGarden("高一三班");
s1.setMath(93.45);
s1.setEnglish(86.54);
s1.setChinese(96.3);
Student s2 = new Student();
s2.setNo("20150002");
s2.setName("刘凯");
s2.setSex("男");
s2.setGarden("高一三班");
s2.setMath(91.45);
s2.setEnglish(84.54);
s2.setChinese(89.43);
dbHelper.AddOper(s1);
dbHelper.AddOper(s2);
初始化两个学生对象,然后调用插入输入库操作
数据插入成功。接下来测试更新操作
dbHelper.UpdateOper("20150002", "English", 90.3);
可以看到,对应学号的学生刘凯的英语成绩已经被修改为90.3
删除操作:
dbHelper.DeleteOper("20150001");
数据库中,对应学号的学生信息已被删除。
测试查找:
dbHelper.SearchOper("20150002");
dbHelper.SearchOper("20150002","Math");
控制台输出了对应的信息。
至此,增删改查的操作都执行了一遍。经过今天的编程练习,我初步了解了JDBC对Mysql数据库的操作。但是,还有很多地方不明白,日后还需要努力深入学习。