JDBC入门(一)基本CRUD操作

java连接数据库步骤(mysql):

加载驱动,获取连接,创建处理命令,执行sql命令,处理结果,关闭资源.

public class JDBCCrud {

	/**
	 * 向表中添加数据 
	 */
	public void insert(String username,String password){
		Connection conn = null;
		Statement stmt = null;
		try {
			//加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获取连接
			conn = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/mydb?user=root&password=9527");
			//创建处理命令
			stmt = conn.createStatement();
			//执行sql命令
			int i = stmt.executeUpdate(
					"insert into user(username,password) values('"+username+"','"+password+"')");
			//处理结果
			if(i>0){
				System.out.println("操作成功,影响数据行数:"+i);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				//alt+shift+z
				if(stmt != null)stmt.close();
				if(conn != null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public void login(String username,String password){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&password=9527");
			stmt = conn.createStatement();
			//执行查询操作获取结果集
			rs = stmt.executeQuery("select * from user where username='"+username+"' and password='"+password+"'");
			//取出结果集中的数据
			if(rs.next()){
				//根据数据表中的列名称以及类型从结果集中取出指定的列数据
				int id = rs.getInt("id");
				String uname = rs.getString("username");
				String pword = rs.getString("password");
				System.out.println(id+"--"+uname+"--"+pword);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs != null)rs.close();
				if(stmt != null)stmt.close();
				if(conn != null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 *查询所有操作
	 *
	 */
	public void queryAll(){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&password=9527");
			stmt = conn.createStatement();
			//执行查询操作获取结果集
			rs = stmt.executeQuery("select id,username,password from user");
			//取出结果集中的数据
			while(rs.next()){
				//根据数据表中的列名称以及类型从结果集中取出指定的列数据
				int id = rs.getInt("id");
				String uname = rs.getString("username");
				String pword = rs.getString("password");
				System.out.println(id+"--"+uname+"--"+pword);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs != null)rs.close();
				if(stmt != null)stmt.close();
				if(conn != null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 删除数据操作
	 */ 
	public void delete(int id){
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&password=9527");
			stmt = conn.createStatement();
			int i = stmt.executeUpdate("delete from user where id="+id+"");
			if(i > 0){
				System.out.println("操作成功,影响数据行数:"+i);
				System.out.println("删除"+id+"号用户成功!");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(stmt != null)stmt.close();
				if(conn != null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 更新数据操作
	 */
	public void updete(int id,String username,String password){
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?user=root&password=9527");
			stmt = conn.createStatement();
			int i = stmt.executeUpdate(
					"update user set username='"+username+"',password='"+password+"' where id="+id+"");
			if(i>0){
				System.out.println("操作成功,影响数据行数:"+i);
				System.out.println("修改成功!");
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(stmt != null)stmt.close();
				if(conn != null)conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}	
	}
	
	
	public static void main(String[] args) {
		int id = 1;
		String username = "rose";
		String password = "654321";
		JDBCCrud jdbc = new JDBCCrud();
		//jdbc.insert(username,password);
		//jdbc.queryAll();
		//jdbc.updete(id, username, password);
		//jdbc.delete(id);
		jdbc.login(username,password);
	}
}