使用Statement执行sql语句

来源：互联网发布：算法工程师培训班编辑：程序博客网时间：2024/06/05 18:21

Demo2.java

package com.cn.statement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import org.junit.Test;import com.cn.Util.JdbcUtil;/*** Author：Liu Zhiyong（QQ：1012421396）* Version：Version_1* Date：2017年3月17日11:01:28* Desc：使用Statement执行sql语句1.使用jdbc创建一张员工表员工表字段：编号，姓名，性别，年龄，职位，邮箱，电话2.使用jdbc对员工表执行以下操作：1）插入一条数据2）修改一条数据3）删除一条数据4）查询并打印所有员工数据*/public class Demo2 {private String url = "jdbc:mysql://localhost:3306/mydb";private String user = "root";private String password = "root";/** * 使用Statement执行DDL语句 */@Testpublic void test1(){Connection conn = null;Statement stmt = null;try {//1.注册MySQL驱动Class.forName("com.mysql.jdbc.Driver");//2.获取连接对象conn = DriverManager.getConnection(url, user, password);//3.创建StateMentstmt = conn.createStatement();//4.准备sqlString sql = "create table employee(eid int primary key auto_increment, ename varchar(20), gender varchar(2), dept varchar(20), email varchar(30), phone varchar(20) )";//5.执行sql，并返回结果int count = stmt.executeUpdate(sql);//6.输出结果System.out.println(count);} catch (Exception e) {throw new RuntimeException(e);}finally{//7.关闭资源（顺序：后打开，先关闭）if(stmt != null){try {stmt.close();} catch (SQLException e) {System.out.println("Statement关闭失败！");throw new RuntimeException(e);}}if(conn != null){try {conn.close();} catch (SQLException e) {System.out.println("Connection关闭失败！");throw new RuntimeException(e);}}}}/** *  * 使用Statement执行DML语句 *  * 插入insert */@Testpublic void test2()  {Connection conn = null;Statement stmt = null;try {/** * 抽取代码 * 通过工具获取连接对象 */conn = JdbcUtil.getConnection();//3.创建Statement对象stmt = conn.createStatement();//4.准备sqlString sql = "INSERT INTO employee(ename, gender, dept,email,phone) VALUES('刘先森', '男', '开发部', '1012421396@qq.com', '18071897425')";//5.执行sqlint count = stmt.executeUpdate(sql);System.out.println(count);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();throw new RuntimeException(e);}finally{JdbcUtil.close(conn, stmt);}} /** * 使用Statement执行DML语句 *  * 修改update */@Testpublic void test3()  {Connection conn = null;Statement stmt = null;//模拟用户输入String name = "张馨予";int id = 3;try {//通过工具获取连接对象conn = JdbcUtil.getConnection();//3.创建Statement对象stmt = conn.createStatement();//4.准备sqlString sql = "UPDATE employee SET ename='" + name + "' WHERE eid = " + id;//5.执行sqlint count = stmt.executeUpdate(sql);System.out.println(count);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();throw new RuntimeException(e);}finally{JdbcUtil.close(conn, stmt);}}/** * 使用Statement执行DML语句 *  * 删除delete */@Testpublic void test4()  {Connection conn = null;Statement stmt = null;//模拟用户输入int id = 6;try {//通过工具获取连接对象conn = JdbcUtil.getConnection();//3.创建Statement对象stmt = conn.createStatement();//4.准备sqlString sql = "delete from employee WHERE eid = " + id;//5.执行sqlint count = stmt.executeUpdate(sql);System.out.println(count);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();throw new RuntimeException(e);}finally{JdbcUtil.close(conn, stmt);}}/** * 使用Statement执行DQL语句（查询操作） */@Testpublic void test5() {Connection conn = null;Statement stmt = null;try {//获取连接对象conn = JdbcUtil.getConnection();//创建Statement对象stmt = conn.createStatement();//准备sql语句String sql = "select * from employee";//执行sqlResultSet rs = stmt.executeQuery(sql);System.out.println("根据列的索引取值");while(rs.next()){System.out.println(rs.getInt(1) + "#" + rs.getString(2) + "#" + rs.getString(3) + "#" + rs.getString(4) + "#" + rs.getString(5) + "#" + rs.getString(6));}System.out.println("根据列的名称取值");rs = stmt.executeQuery(sql);while(rs.next()){System.out.println(rs.getInt("eid") + "#" + rs.getString("ename") + "#" + rs.getString("gender") + "#" + rs.getString("dept") + "#" + rs.getString("email") + "#" + rs.getString("phone"));}} catch (Exception e) {throw new RuntimeException(e);}finally{JdbcUtil.close(conn, stmt);}}}

抽取jdbc获取Connection对象和关闭Connection对象和Statement对象的工具类

JdbcUtil.java

package com.cn.Util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;/** * jdbc的工具类 * @author liuzhiyong * */public class JdbcUtil {private static String url = "jdbc:mysql://localhost:3306/mydb";private static String user = "root";private static String password = "root";/** * 静态代码块（只调用一次） */static{//注册驱动程序try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();System.out.println("驱动程序注册出错！");}}/** * 获取连接对象的方法 */public static Connection getConnection(){try {Connection conn = DriverManager.getConnection(url, user, password);return conn;} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();throw new RuntimeException(e);}}/** * 释放资源的方法 */public static void close(Connection conn, Statement stmt){//关闭资源（顺序：后打开，先关闭）if(stmt != null){try {stmt.close();} catch (SQLException e) {System.out.println("Statement关闭失败！");throw new RuntimeException(e);}}if(conn != null){try {conn.close();} catch (SQLException e) {System.out.println("Connection关闭失败！");throw new RuntimeException(e);}}}}

0 0