JDBC:Statement语句

1、Statement语句是SQL语句的描述，使用它可以操作各种SQL语句，包括DDL(数据定义语句，如创建表）、DML(数据操作语言，如insert，update，delete）、DSL(数据控制语言)等。
2、使用Statement创建表

//创建表
	static void createTable() {
		Connection conn = DBUtil.open();
		String sql = "create table UserTbl(id int primary key auto_increment,name varchar(20))";
		try {
			Statement stmt = conn.createStatement();
			stmt.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
	}

3、插入数据

//添加数据
	static void insert() {
		Connection conn = DBUtil.open();
		String sql = "insert into UserTbl(name) values('guyang')";
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
	}

4、更新数据

static void update() {
		Connection conn = DBUtil.open();
		String sql = "update UserTbl set name = 'big guyang' where id >3";
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
	}

5、删除数据

static void delete() {
		Connection conn = DBUtil.open();
		String sql = "delete from UserTbl where id = 1";
		try {
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
	}

6、查询数据

//查询
	static void query() {
		Connection conn = DBUtil.open();
		String sql = "select id,name from UserTbl";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			//遍历结果集
			while(rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				System.out.println(id+","+name);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
	}

将数据封装成对象方式查询

public class User {
	private int id;
	private String name;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String toString() {
		return id+":"+name;
	}
}

//查询
	static List<User> query2() {
		Connection conn = DBUtil.open();
		String sql = "select id,name from UserTbl";
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			List<User> list = new ArrayList<User>();
			//遍历结果集
			while(rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				User u = new User();
				u.setId(id);
				u.setName(name);
				list.add(u);
//				System.out.println(id+","+name);
			}
			System.out.println(list);
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
		return null;
	}

项目路径：E:\eclipse-workspace\JDBCdemo