1.实验环境Eclipse Mysql数据库
新建数据库表
create database db_database01;
use db_database01;
create table students (ID int(11),Name varchar(25),Sex varchar(25),Age varchar(25));
整体框架图如下
2.新建3个包application放主文件,daos放数据层,users放用户封装。
具体代码如下:
(1)JDBCOperation.java
package com.hznu.qjc.application;import java.util.Scanner;
import com.hznu.qjc.daos.DAO;
import com.hznu.qjc.users.Student;
public class JDBCOperation {
public static void main(String args[]) {
do{
System.out.println("++++++++++欢迎使用数据库增删改查功能++++++++++++");
System.out.println("+++1.insert 2.delete 3.update 4.query+++");
System.out.println("+++++++++++++请输入您选择的操作+++++++++++++++");
Scanner input = new Scanner(System.in);
int c = input.nextInt();
switch(c){
case 1: System.out.println("Insert");
DAO.getAll();
DAO.insert(new Student("Achilles", "Male", "14"));
break;
case 2: System.out.println("Delete");
DAO.delete("Achilles");
DAO.getAll();
break;
case 3: System.out.println("Update");
DAO.getAll();
DAO.update(new Student("Bean", "", "7"));
break;
case 4: System.out.println("Query");
DAO.getAll();
break;
default:
System.out.println("consonant");
}
} while(true);
}
}
(2)DAO.java
package com.hznu.qjc.daos;
import com.hznu.qjc.users.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DAO {
private static Connection getConn() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver"); //classLoader,加载对应驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url = "jdbc:mysql://localhost:3306/db_database01";
String username = "root";//数据库账户,一般为root
String password = "";//数据库密码
try{
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static int insert(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "insert into students (Name,Sex,Age) values(?,?,?)";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, student.getName());
pstmt.setString(2, student.getSex());
pstmt.setString(3, student.getAge());
i = pstmt.executeUpdate();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
public static int update(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
public static Integer getAll() {
Connection conn = getConn();
String sql = "select * from students";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement)conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
int col = rs.getMetaData().getColumnCount();
System.out.println("============================");
while (rs.next()) {
for (int i = 1; i <= col; i++) {
System.out.print(rs.getString(i) + "\t");
if ((i == 2) && (rs.getString(i).length() < 8)) {
System.out.print("\t");
}
}
System.out.println("");
}
System.out.println("============================");
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static int delete(String name) {
Connection conn = getConn();
int i = 0;
String sql = "delete from students where Name='" + name + "'";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
}
(3)Student.java
package com.hznu.qjc.users;
public class Student {
protected String Id;
protected String Name;
protected String Sex;
protected String Age;
public Student(String Name, String Sex, String Age) {
this.Id = null; //default
this.Name = Name;
this.Sex = Sex;
this.Age = Age;
}
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 getSex() {
return Sex;
}
public void setSex(String Sex) {
this.Sex = Sex;
}
public String getAge() {
return Age;
}
public void setage(String Age) {
this.Age = Age;
}
}
3.实验结果如下