工具:eclipse
MySQL5.6
MySQL连接驱动:mysql-connector-java-5.1.27.jar
加载驱动:
1. 在工程目录中创建lib文件夹,将下载好的JDBC放到该文件夹下,如下图所示:
2. 右键工程名,在java build path中的Libraries分页中选择Add JARs...,选择刚才添加的JDBC,如下图:
数据包准备:
在数据库sqltestdb中创建如下数据表emp:
1 2 3 4 5 6 7 |
|
添加数据:
连接数据库并读取数据:
数据库名称:sqltestdb
数据包名称:emp
端口号:3306
用户名:root
密码:root
package com.cn.peitest.tongBu; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TongBu { public static void main(String[] args) { //声明Connection对象 Connection con; //驱动程序名 String driver = "com.mysql.jdbc.Driver"; //URL指向要访问的数据库名mydata String url = "jdbc:mysql://localhost:3306/pei_test"; //MySQL配置时的用户名 String user = "root"; //MySQL配置时的密码 String password = "123456"; //遍历查询结果集 try { //加载驱动程序 Class.forName(driver); //1.getConnection()方法,连接MySQL数据库!! con = DriverManager.getConnection(url,user,password); if(!con.isClosed()) System.out.println("Succeeded connecting to the Database!"); //2.创建statement类对象,用来执行SQL语句!! Statement statement = con.createStatement(); //要执行的SQL语句 String sql = "select * from blade_user"; //3.ResultSet类,用来存放获取的结果集!! ResultSet rs = statement.executeQuery(sql); System.out.println("-----------------"); System.out.println("执行结果如下所示:"); System.out.println("-----------------"); System.out.println("姓名" + " " + "职称"); System.out.println("-----------------"); String job = null; String id = null; while(rs.next()){ //获取stuname这列数据 job = rs.getString("job"); //获取stuid这列数据 id = rs.getString("ename"); //输出结果 System.out.println(id + " " + job); } rs.close(); con.close(); } catch(ClassNotFoundException e) { //数据库驱动类异常处理 System.out.println("Sorry,can`t find the Driver!"); e.printStackTrace(); } catch(SQLException e) { //数据库连接失败异常处理 e.printStackTrace(); }catch (Exception e) { // TODO: handle exception e.printStackTrace(); }finally{ System.out.println("数据库数据成功获取!!"); } } }
运行结果:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
|
增加、删除和修改数据:
增加数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
|
运行结果:
更新数据:
1 2 3 4 5 6 |
|
更改结果:
删除数据:
1 2 3 4 5 6 |
|
删除结果:
private List<Map<String, Object>> list = new ArrayList<Map<String,Object>>(); public String queryAll() { Connection conn = null; Statement sta = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/easyui", "root", "root"); sta = conn.createStatement(); rs = sta.executeQuery("select * from e_user"); ResultSetMetaData md = rs.getMetaData(); //获得结果集结构信息,元数据 int columnCount = md.getColumnCount(); //获得列数 while (rs.next()) { Map<String,Object> rowData = new HashMap<String,Object>(); for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), rs.getObject(i)); } list.add(rowData); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return "success"; }