【学习摘记】马士兵JDBC技术_复习总结_最重要的四段代码

【JDBC最重要的四段代码】

1、JDBC初步（select语句）

package com.sql1;  
import java.sql.*; 

public class TestJDBC {

	public static void main(String[] args) {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/xiaowu?useUnicode=true&characterEncoding=utf8", "root", "xiaowu");                                                

			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from single_game");
			while(rs.next()) {
				System.out.print(rs.getString(1)+"\t");
				System.out.print(rs.getString(2)+"\t");
				System.out.print(rs.getString(3)+"\t");
				System.out.print(rs.getString(4)+"\t");
				System.out.println();
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(rs != null) {
					rs.close();
					rs = null;
				}
				if(stmt != null) {
					stmt.close();
					stmt = null;
				}
				if(conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

2、DML：stmt——不含任何参数，直接写insert语句

package com.sql1;
import java.sql.*;

public class TestDML {

	public static void main(String[] args) {
		Connection conn=null;
		Statement stmt=null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/xiaowu?useUnicode=true&characterEncoding=utf8","root","xiaowu");
			stmt=conn.createStatement();
			String sql="insert into single_game values(88,'仙剑','武侠','六年级','8')";
			stmt.executeUpdate(sql);
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(stmt != null) {
					stmt.close();
					stmt = null;
				}
				if(conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

3、DML：pstmt——先写含参数的insert语句，再设置参数

package com.sql1;
import java.sql.*;

public class TestPrepStmt {

	public static void main(String[] args) {
		Connection conn=null;
		PreparedStatement pstmt=null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/xiaowu?useUnicode=true&characterEncoding=utf8","root","xiaowu");
			String sql="insert into single_game values (?,?,?,?,?)";
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, 44);
			pstmt.setString(2, "侠岚");
			pstmt.setString(3, "武侠");
			pstmt.setString(4, "高中");
			pstmt.setString(5, "1");
			pstmt.executeUpdate();
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if(pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if(conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

4、Transaction

package com.sql1;
import java.sql.*;
public class TestTransaction {


	public static void main(String[] args) {
		
		Connection conn = null;
		Statement stmt = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/xiaowu?useUnicode=true&characterEncoding=utf8","root","xiaowu");
			
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			stmt.addBatch("insert into single_game values (1, '流星蝴蝶剑', '武侠', '初中', '1')");
			stmt.addBatch("insert into single_game values (2, '仙剑奇侠传1', '武侠', '小学', '2')");
			stmt.addBatch("insert into single_game values (13, '仙剑奇侠传2', '武侠', '小学', '3')");
			stmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch(SQLException e) {
			
			e.printStackTrace();
			
			try {
				if(conn != null)
				{
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally {
			try {
				if(stmt != null)
					stmt.close();
				if(conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		

	}

}