JDBC连接mysql数据库

       如图，把mysql驱动程序的jar包放在lib下，点击JSPStudy的属性，和图中一样，再选择Add JARs ,把驱动程序加进去在java resources下的src目录下创建JDBCTest类

   

1.做一个最简单的查询操作（基本的五个步骤: 加载驱动  打开链接  执行查询   处理结果 清理环境）

package com.fc.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;import java.sql.ResultSet;public class JDBCTest {public static void main(String[] args) {String sql="SELECT* FROM user ";Connection conn=null;//当前的数据库链接Statement st = null; //用于向数据库发送sql语句ResultSet rs = null; //结果集：封装了从数据库中查询到的数据try {Class.forName("com.mysql.jdbc.Driver");//用来注册mysql的驱动程序conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/online_examination","root","");//获取连接（online_examination是使用的数据库名字，后边两个参数是用户名和密码）st = conn.createStatement();rs = (ResultSet) st.executeQuery(sql);//执行sql语句while(rs.next()){System.out.println(rs.getString("username")+ "  ");System.out.println(rs.getString("passwd")+ "  ");System.out.println(rs.getString("role")+ "  ");System.out.println();}} catch (Exception e) {e.printStackTrace(); //打印异常信息}finally{  //资源清理的逻辑  (按照由小到大的顺序关闭)try {rs.close();} catch (Exception e2) {// TODO: handle exception}try {st.close();} catch (Exception e3) {// TODO: handle exception}try {conn.close();} catch (Exception e4) {// TODO: handle exception}}}}

eclipse使用小技巧 ：
   1. 自动自动生成main方法  输入main，alt+/  选择第一个
   2. 自动导入需要的类和接口   ctrl+shift+o   在ResultSet rs = null; 这行写完可以试试
   3. 自动生成try catch，输入try，alt+/  选择第一个
   4. 自动生成输出语句： 输入sysout，alt+/
   5. 代码自动对齐  ctrl+shift+f

2.把增删改查封装为函数：

package com.fc.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;import java.sql.ResultSet;public class JDBCTest {public static Connection getConnection() {Connection conn= null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/online_examination","root","");} catch (Exception e) {e.printStackTrace();}return conn;}public static void insert(){Connection conn = getConnection();try {String sql ="INSERT INTO user(username,passwd,role)"+"VALUES('fc','123456','user')";Statement st =conn.createStatement();int count = st.executeUpdate(sql);System.out.println("向用户表里插入了"+count+"条数据");conn.close();} catch (Exception e) { e.printStackTrace();}}public static void update(){Connection conn = getConnection();try {String sql ="UPDATE user SET passwd='123'WHERE username='fc'";Statement st =conn.createStatement();int count = st.executeUpdate(sql); System.out.println("向用户表里 更新了"+count+"条数据");conn.close();} catch (Exception e) {e.printStackTrace();}}public static void delete(){Connection conn = getConnection();try {String sql ="DELETE FROM user WHERE username='fc'";Statement st =conn.createStatement();int count = st.executeUpdate(sql); System.out.println("向用户表里 删除了"+count+"条数据");conn.close();} catch (Exception e) {e.printStackTrace();}}public static void main(String[] args) {//  insert();//  update(); delete();}}

3.数据的一致性问题：

  问题：本来用户表中只有一个sys用户，现在向里面同时插入fc和sys用户，因为用户名是主键，运行下面代码只能插入fc用户：

package com.fc.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class Transaction {public static Connection getConnection() {Connection conn = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/online_examination", "root","");} catch (Exception e) {e.printStackTrace();}return conn;}public static void insertUserData(){Connection conn = getConnection();try {String sql = "INSERT INTO user(username,passwd,role)"+ "VALUES('fc','123456','user')";Statement st = conn.createStatement();int count = st.executeUpdate(sql);System.out.println("向用户表里插入了" + count + "条数据");conn.close();} catch (Exception e) {e.printStackTrace();}}public static void insertUserData_1() {Connection conn = getConnection();try {String sql = "INSERT INTO user(username,passwd,role)"+ "VALUES('sys','123456','user')";Statement st = conn.createStatement();int count = st.executeUpdate(sql);System.out.println("向用户表里插入了" + count + "条数据");conn.close();} catch (Exception e) {e.printStackTrace();}}public static void main(String[] args) {insertUserData();insertUserData_1();}}

但是我们想做的是要么这两行同时插入成功，要么都不成功,改进代码如下：
    1.首先去掉异常捕获代码，将异常抛出给上层调用方法
    2.使用Connection 作为方法参数
    3.去掉方法体内的连接和关闭代码
    4.在主函数中重新调用

package com.fc.jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Transaction {public static Connection getConnection() {Connection conn = null;try {Class.forName("com.mysql.jdbc.Driver");conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/online_examination", "root","");} catch (Exception e) {e.printStackTrace();}return conn;}public static void insertUserData(Connection conn) throws SQLException{String sql = "INSERT INTO user(username,passwd,role)"+ "VALUES('fc','123456','user')";Statement st = conn.createStatement();int count = st.executeUpdate(sql);System.out.println("向用户表里插入了" + count + "条数据");}public static void insertUserData_1 (Connection conn)  throws SQLException{String sql = "INSERT INTO user(username,passwd,role)"+ "VALUES('sys','123456','user')";Statement st = conn.createStatement();int count = st.executeUpdate(sql);System.out.println("向用户表里插入了" + count + "条数据");}public static void main(String[] args) {Connection conn=getConnection();try {conn.setAutoCommit(false);//关闭事物自动提交功能insertUserData(conn);insertUserData_1(conn);conn.commit();// 自己提交事务} catch (SQLException e) {System.out.println("---------捕获到异常---------");e.printStackTrace();try {conn.rollback();//回滚System.out.println("---------回滚成功---------");} catch (Exception e2) {e2.printStackTrace();}}finally{ // 资源清理try {if(conn != null){conn.close();}} catch (Exception e3) {e3.printStackTrace();}}}}