JDBC 操作mysql 实现增删查改
package com.eduk.demo;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCOperation {
private static Connection conn = null;//声明数据库回话对象,Connection 这是用来维护数据库连接信息
private static Statement sts = null;// Statement 用来操作数据，也是执行SQL语句（父类）
static class Student {
private String Id;
private String Name;
private String Sex;
private String Age;
private String Course;
private String Time;
Student(String Id, String Name, String Sex, String Age,String Course,String Time) {
this.Id = Id; //default
this.Name = Name;
this.Sex = Sex;
this.Age = Age;
this.Course = Course;
this.Time = Time;
}
public String getCourse() {
return Course;
}
public void setCourse(String course) {
Course = course;
}
public String getTime() {
return Time;
}
public void setTime(String time) {
Time = time;
}
public void setAge(String age) {
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;
}
}
private static Connection getConn() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/uus";
String username = "root";
String password = "root";
Connection conn = null;
try {
Class.forName(driver); //classLoader,加载对应驱动
conn = (Connection) DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private static int CreateDatabase(String database) {
Connection conn = getConn();
int i = 0;
String sql = "CREATE DATABASE "+database;
//String sql = "DROP DATABASE "+database;
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
i = pstmt.executeUpdate();
System.out.println("resutl: " + i);
System.out.println("创建数据库" + database + "成功！");
pstmt.close();
conn.close();
} catch (SQLException e) {
System.out.println("创建数据表失败！");
e.printStackTrace();
}
return i;
}
private static int CreateTable() {
Connection conn = getConn();
int i = 0;
// UPDATE students set age="23" where name="Achilles";
// UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;
String sql = "CREATE TABLE student " +
"(sid INTEGER(20) not NULL, " +
" name VARCHAR(255) not NULL, " +
" sex VARCHAR(255), " +
" age INTEGER(4), " +
" course FLOAT(8), " +
" time VARCHAR(32), " +
" PRIMARY KEY ( sid ))ENGINE=InnoDB DEFAULT CHARSET=UTF8; ";
// String sql = "update student set name='" + student.getName() + "' where sid=" + student.getId();
//String sql = "update student set name='送终极' where sid=12;";
try {
sts = conn.createStatement();//初始化statement
int f = sts.executeUpdate(sql);
System.out.println("创建数据表成功！");
} catch (SQLException e) {
System.out.println("创建数据表失败！");
e.printStackTrace();
}
finally {
try {
// res.close();//可写可不写
sts.close();//可写可不写
conn.close();//一定要写
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
private static int insert(Student student) {
Connection conn = getConn();
int i = 0;
String sql = "insert into student (SID,Name,Sex,Age,Course,Time) values(?,?,?,?,?,?)";
PreparedStatement pstmt;
try {
pstmt = (PreparedStatement) conn.prepareStatement(sql);
pstmt.setString(1, student.getId());
pstmt.setString(2, student.getName());
pstmt.setString(3, student.getSex());
pstmt.setString(4, student.getAge());
pstmt.setString(5, student.getCourse());
pstmt.setString(6, student.getTime());
i = pstmt.executeUpdate();
pstmt.close();
conn.close();
System.out.println("插入成功！");
} catch (SQLException e) {
System.out.println("插入失败！");
e.printStackTrace();
}
return i;
}
private static int update(Student student) {
Connection conn = getConn();
int i = 0;
// UPDATE students set age="23" where name="Achilles";
// UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;
String sql = "update student set Name='" + student.getName() + "',sex='" + student.getSex() + "', age = '" +
student.getAge() + "',course = '" + student.getCourse() + "', time = '" + student.getTime() + "' where sid='" + student.getId() + "'";
// String sql = "update student set name='" + student.getName() + "' where sid=" + student.getId();
//String sql = "update student set name='送终极' where sid=12;";
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;
}
private static Integer getAll() {
Connection conn = getConn();
String sql = "select * from student order by sid desc";
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;
}
private 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;
}
public static void main(String args[]) {
// JDBCOperation.CreateDatabase("uus");
// JDBCOperation.CreateTable();
JDBCOperation.insert(new Student("8","阳春白雪", "女", "23","95",""));
// JDBCOperation.getAll();
// JDBCOperation.update(new Student("12","JAC", "Femail","26", "90",""));
// JDBCOperation.update(new Student("4","欧阳春","男","29", "90",""));
// JDBCOperation.delete("Achilles");
JDBCOperation.getAll();
}
}
