(1)定义记录的类(可选)
这样做主要是为了便于操作和接口定义,是非必须的。
static class Student { private String Id; private String Name; private String Sex; private String Age; 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; } }
(2)连接的获取
在操作前必须先获取与数据库的连接。
driver、url的格式同样可以参考各种数据库对应的jar包、驱动类名和URL格式。
private static Connection getConn() { String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/samp_db"; String username = "root"; String password = ""; 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; }
(3)insert
private 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; }
(4)update
private 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; }
(5)select
以select * from XXX为例。
private 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; }
(6)delete
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; }
3.2 全部代码实现
package com.runoob;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import java.sql.*;
public class BasicOperation {
static class students {
private String Id;
private String Name;
private String Sex;
private String Age;
students(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;
}
}
private static Connection getConn(){
String driver="com.mysql.jdbc.Driver";
String url="jdbc:mysql://localhost:3306/lh_db?useUnicode=true&characterEncoding=utf-8&useSSL=false";
String username="root";
String password="123456";
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 insert(students 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;
}
private static int update(students 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("result:"+i);
pstmt.close();
conn.close();
}catch(SQLException e){
e.printStackTrace();
}
return i;
}
private 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;
}
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("result:"+i);
System.out.println("result:"+i);
pstmt.close();
}catch(SQLException e){
e.printStackTrace();
}
return i;
}
public static void main(String args[]){
//BasicOperation.getAll();
//BasicOperation.insert(new students("Amy","female","68"));
BasicOperation.getAll();
BasicOperation.update(new students("Tom","male","37"));
//BasicOperation.getAll();
BasicOperation.delete("Amy");
BasicOperation.getAll();
}
}
3.3 测试结果
在测试前,需要在系统中打开对应数据库的服务。MySQL在Windows下的启动命令为
net start mysql
测试代码
Eclipse中的输出
4. 错误解决方法
1.import com.mysql cannot be resloved失败
原因:没有导入mysql包,需手动导入
方法:
File-Properties-java Build Path-Projects-add external jars-找到你下载的mysql.jar包导入即可。