jdbc 练习

最新推荐文章于 2025-09-20 08:00:00 发布

最新推荐文章于 2025-09-20 08:00:00 发布 · 126 阅读

标签

#数据库 #java

jdbc 专栏收录该内容

1 篇文章

订阅专栏

package test;

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

public class TestJdbc {

	public static void main(String[] args) {
		Connection con = null;
		Statement st = null;
		ResultSet set = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/antTest";
			String userName = "root";
			String password = "root";
			con = DriverManager.getConnection(url, userName, password);
			st = con.createStatement();
			st.execute("select * from users");
			set = st.getResultSet();
			while (set.next()) {
				System.out.println(set.getString("names"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (set != null) {
					set.close();
					set = null;
				}
				if (st != null) {
					st.close();
					st = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

插入语句:

package test;

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

public class TestDML {

	public static void main(String[] args) {
		Connection con = null;
		Statement st = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/antTest";
			String userName = "root";
			String password = "root";
			con = DriverManager.getConnection(url, userName, password);
			st = con.createStatement();
			String name = "小蛮";
			int age = 21;
			String sql = "insert into users (name ,age) values ('"+name+"',"+age+") ";
			System.out.println(sql);
			st.execute(sql);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
					st = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}

}

 PreparedStatement

package test;

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

public class TestPreparedStatement {

	
	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement st = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/antTest";
			String userName = "root";
			String password = "root";
			con = DriverManager.getConnection(url, userName, password);
			//preparedStatement不再辛苦的拼字符串，可以灵活的指定sql中的变量
			String sql = "insert into users (name ,age) values (?,?) ";
			st = con.prepareStatement(sql);
			st.setString(1, "wang饿");;
			st.setInt(2, 23);
			st.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
					st = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

 利用CallableStatement 接口来实现对mysql存储过程的调用

package test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestCallableStatement {

	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/antTest";
		String userName = "root";
		String password = "root";
		CallableStatement cs = null;
		Connection con = null;
		ResultSet set = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url, userName, password);
			cs = con.prepareCall("call pre_add(?)");
			cs.setInt(1, 1);
			cs.execute();
			set = cs.getResultSet();
			while (set.next()) {
				System.out.println(set.getString("name"));
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (set != null) {
					set.close();
					set = null;
				}
				if (cs != null) {
					cs.close();
					cs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

存储过程如下：

DELIMITER $$

USE `anttest`$$

DROP PROCEDURE IF EXISTS `pre_add`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pre_add`(IN d INT)
BEGIN
  DECLARE c INT (2) ;
  SELECT 
   *
  FROM
    antTest.`users` 
  WHERE id > d ;
END$$

DELIMITER ;

 批量处理：

package test;

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

public class TestBatch {

	public static void main(String[] args) {

		Connection con = null;
		PreparedStatement st = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/antTest";
			String userName = "root";
			String password = "root";
			con = DriverManager.getConnection(url, userName, password);
			//preparedStatement不再辛苦的拼字符串
			String sql = "insert into users (name ,age) values (?,?) ";
			st = con.prepareStatement(sql);
			st.setString(1, "sdfs");;
			st.setInt(2, 24);
			st.addBatch();
			st.setString(1, "wangers");;
			st.setInt(2, 25);
			st.addBatch();
			System.out.println(st.executeBatch());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (st != null) {
					st.close();
					st = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	}

}

 Transaction

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
//Transaction
public class TestBatch {

	public static void main(String[] args) {

		Connection con = null;
		PreparedStatement st = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/antTest";
			String userName = "root";
			String password = "root";
			con = DriverManager.getConnection(url, userName, password);
			con.setAutoCommit(false);
			String sql = "insert into users (name ,age) values (?,?) ";
			st = con.prepareStatement(sql);
			st.setString(1, "sdfs");
			;
			st.setInt(2, 24);
			st.addBatch();
			st.setString(1, "wangers");
			;
			st.setInt(2, 25);
			st.addBatch();
			System.out.println(st.executeBatch());
			con.commit();
			con.setAutoCommit(true);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				if (con != null) {
					con.rollback();
					con.setAutoCommit(true);
				}
			} catch (SQLException er) {
				er.printStackTrace();
			}
		} finally {
			try {
				if (st != null) {
					st.close();
					st = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}