【Java学习笔记】JDBC连接mySql数据库

本文链接：https://blog.csdn.net/VisionDo/article/details/50297139

------<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数据库的操作。但是，还有很多地方不明白，日后还需要努力深入学习。