使用Statement执行sql语句

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语句
	 */
	@Test
	public 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.创建StateMent
			stmt = conn.createStatement();
			
			//4.准备sql
			String 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
	 */
	@Test
	public void test2()  {
		Connection conn = null;
		Statement stmt = null;
		try {
			/**
			 * 抽取代码
			 * 通过工具获取连接对象
			 */
			conn = JdbcUtil.getConnection();
			
			//3.创建Statement对象
			stmt = conn.createStatement();
			
			//4.准备sql
			String sql = "INSERT INTO employee(ename, gender, dept,email,phone) VALUES('刘先森', '男', '开发部', '1012421396@qq.com', '18071897425')";
			
			//5.执行sql
			int count = stmt.executeUpdate(sql);
			System.out.println(count);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			JdbcUtil.close(conn, stmt);
		}
	}
	
	
	 /**
	 * 使用Statement执行DML语句
	 * 
	 * 修改update
	 */
	@Test
	public void test3()  {
		Connection conn = null;
		Statement stmt = null;
		//模拟用户输入
		String name = "张馨予";
		int id = 3;
		try {
			//通过工具获取连接对象
			conn = JdbcUtil.getConnection();
			
			//3.创建Statement对象
			stmt = conn.createStatement();
			
			//4.准备sql
			String sql = "UPDATE employee SET ename='" + name + "' WHERE eid = " + id;
			
			//5.执行sql
			int count = stmt.executeUpdate(sql);
			System.out.println(count);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			JdbcUtil.close(conn, stmt);
		}
	}
	
	/**
	 * 使用Statement执行DML语句
	 * 
	 * 删除delete
	 */
	@Test
	public void test4()  {
		Connection conn = null;
		Statement stmt = null;
		//模拟用户输入
		int id = 6;
		try {
			//通过工具获取连接对象
			conn = JdbcUtil.getConnection();
			
			//3.创建Statement对象
			stmt = conn.createStatement();
			
			//4.准备sql
			String sql = "delete from employee WHERE eid = " + id;
			
			//5.执行sql
			int count = stmt.executeUpdate(sql);
			System.out.println(count);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally{
			JdbcUtil.close(conn, stmt);
		}
	}
	
	/**
	 * 使用Statement执行DQL语句(查询操作)
	 */
	@Test
	public void test5() {
		
		Connection conn = null;
		Statement stmt = null;
		try {
			//获取连接对象
			conn = JdbcUtil.getConnection();
			//创建Statement对象
			stmt = conn.createStatement();
			//准备sql语句
			String sql = "select * from employee";
			//执行sql
			ResultSet 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 block
			e.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 block
			e.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);
			}
		}
	}
	
}
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值