java对mysql的操作

一、下载

首先要下载mysql的java驱动jar包，mysql-connector-java_8.0.12，下载网址：https://dev.mysql.com/downloads/connector/j/，这个是在mysql官网的下载地址，根据系统下载相应的版本。下载好jar包后记得在eclipse里Build Path一下，项目就可以运行了。

（附：我是用的是Deepin所以下载Debian，Deepin下：在Debian下载的deb包解压后再提取data.tar.xz里面./usr/share/java里面可以看到mysql-connector-java-8.0.12.jar就是我们需要的jar包了）。

二、代码

代码总共分为六个类：连接类，对数据库增、删、查、改、创建类（5个），如下：

1.连接类

package until;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQLconnection {			
	private static final String DBDRIVER = "com.mysql.jdbc.Driver";									//驱动程序名
	private static final String DBURL = "jdbc:mysql://localhost:3306/testdatabases";					//URL指向要访问的数据库名mydata
	private static final String DBUSER = "root";														//MySQL配置时的用户名
	private static final String DBPASSWORD = "123456"; 												//MySQL配置时的密码

	public static Connection getConnection(){
		Connection conn = null;													//声明一个连接对象
		try {
			Class.forName(DBDRIVER);											//注册驱动
			conn = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);		//获得连接对象
		} catch (ClassNotFoundException e) {									//捕获驱动类无法找到异常
			e.printStackTrace();										
		} catch (SQLException e) {												//捕获SQL异常
			e.printStackTrace();
		}
		return conn;
	}
	public static void close(Connection conn) {//关闭连接对象
		if(conn != null) {				//如果conn连接对象不为空
			try {
				conn.close();			//关闭conn连接对象对象
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void close(PreparedStatement pstmt) {//关闭预处理对象
		if(pstmt != null) {				//如果pstmt预处理对象不为空
			try {
				pstmt.close();			//关闭pstmt预处理对象
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void close(Statement stmt) {//关闭预处理对象
		if(stmt != null) {				//如果pstmt预处理对象不为空
			try {
				stmt.close();			//关闭pstmt预处理对象
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void close(ResultSet rs) {//关闭结果集对象
		if(rs != null) {				//如果rs结果集对象不为null
			try {
				rs.close();				//关闭rs结果集对象
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

2.创建数据库类

package mysql;

import java.sql.Connection;
import java.sql.Statement;
import until.MySQLconnection;;

public class MySQLCre {
	
	public static void CreDatabase() {
		Connection con;//声明一个连接对象
		//遍历查询结果集
        try {
            con = MySQLconnection.getConnection();//1.调用方法返回连接
            if(!con.isClosed())
                System.out.println("Succeeded connecting to the Database!");
            Statement statement = con.createStatement(); //2.创建statement类对象，用来执行SQL语句！！
            String sql = "CREATE DATABASE spiders DEFAULT CHARACTER SET utf8mb4";//要执行的SQL语句
            statement.executeUpdate(sql);
            MySQLconnection.close(statement);
            MySQLconnection.close(con);
        }	catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
	}
	
    public static void main(String[] args) {
//    	CreDatabase();
    }

}

3.增添数据类

package mysql;

import java.sql.Connection;
import java.sql.Statement;

import until.MySQLconnection;

public class MySQLAdd {
	public static void Add(String[] stuinfo) {
		Connection con;//声明一个连接对象
		//遍历查询结果集
		try {
			con = MySQLconnection.getConnection();//1.调用方法返回连接
			if(!con.isClosed())
				System.out.println("Succeeded connecting to the Database!");
			Statement statement = con.createStatement(); //2.创建statement类对象，用来执行SQL语句！！
			String sql = "INSERT INTO allstuallinfo3(stuid,name) values('%s','%s')";//要执行的SQL语句
			if(statement.executeUpdate(String.format(sql,stuinfo[0],stuinfo[1]))!=0)
				System.out.println("插入成功");
			else 
				System.out.println("插入失败");
			MySQLconnection.close(statement);
			MySQLconnection.close(con);
		}	catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		String[] stuinfo = {"123456","小明"};
		Add(stuinfo);
	}
}

4.删除数据类

package mysql;

import java.sql.Connection;
import java.sql.Statement;

import until.MySQLconnection;

public class MySQLDel {
	public static void Delete(String stuid) {
		Connection con;//声明一个连接对象
		//遍历查询结果集
		try {
			con = MySQLconnection.getConnection();//1.调用方法返回连接
			if(!con.isClosed())
				System.out.println("Succeeded connecting to the Database!");
			Statement statement = con.createStatement(); //2.创建statement类对象，用来执行SQL语句！！
			String sql = "DELETE FROM allstuallinfo3 WHERE stuid='%s'";//要执行的SQL语句
			if(statement.executeUpdate(String.format(sql,stuid))!=0)
				System.out.println("删除成功");
			else 
				System.out.println("删除失败");
			MySQLconnection.close(statement);
			MySQLconnection.close(con);
		}	catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		Delete("123");
	}
}

5.查找数据类

package mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import until.MySQLconnection;

public class MySQLSel {
	public static ArrayList<String> Select(String stuid) {
		ArrayList<String> stu = new ArrayList<>();
		Connection con;//声明一个连接对象
		//遍历查询结果集
		try {
			con = MySQLconnection.getConnection();//1.调用方法返回连接
			if(!con.isClosed())
				System.out.println("Succeeded connecting to the Database!");
			Statement statement = con.createStatement(); //2.创建statement类对象，用来执行SQL语句！！
			String sql = "SELECT * FROM allstuallinfo3 WHERE stuid = %s";//要执行的SQL语句
			ResultSet rs = statement.executeQuery(String.format(sql,stuid));
			while(rs.next()){
				stu.add(rs.getString("stuid").trim());
				stu.add(rs.getString("name").trim());
			}
			MySQLconnection.close(rs);
			MySQLconnection.close(statement);
			MySQLconnection.close(con);
			return stu;
		}	catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return stu;
	}
	public static void main(String[] args) {
		System.out.println(Select("123"));
	}
}

6.修改数据类

package mysql;

import java.sql.Connection;
import java.sql.Statement;

import until.MySQLconnection;

public class MySQLUpd {
	public static void Update(String[] stuinfo) {
		Connection con;//声明一个连接对象
		//遍历查询结果集
		try {
			con = MySQLconnection.getConnection();//1.调用方法返回连接
			if(!con.isClosed())
				System.out.println("Succeeded connecting to the Database!");
			Statement statement = con.createStatement(); //2.创建statement类对象，用来执行SQL语句！！
			String sql = "UPDATE allstuallinfo3 SET name='%s' WHERE stuid = '%s'";//要执行的SQL语句
			if(statement.executeUpdate(String.format(sql,stuinfo[1],stuinfo[0]))!=0)
				System.out.println("更新成功");
			else 
				System.out.println("更新失败");
			MySQLconnection.close(statement);
			MySQLconnection.close(con);
		}	catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		String[] stuinfo = {"123","小刚"};
		Update(stuinfo);
	}
}