Java操作MySql实例

JDBC连接数据库案例

查询

@Override
	public User LoginUser(String uname, String tel) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		//声明变量
		User u=null;
		try {
			//加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获取连接
			conn=(Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=utf8","root", "123456");
			//创建sql命令
			String sql="select * from user where uname=? and tel=?";
			//创建sql命令对象
			ps=(PreparedStatement) conn.prepareStatement(sql);
			//给占位符赋值
			ps.setString(1, uname);
			ps.setString(2, tel);
	
			//执行sql
			rs=ps.executeQuery();
			//遍历结果
				while(rs.next()){
					//给变量赋值
					u=new User();
					u.setUid(rs.getInt("uid"));
					u.setName(rs.getString("uname"));
					u.setAge(rs.getInt("age"));
					u.setSex(rs.getInt("sex"));				
					u.setTel(rs.getString("tel"));
				}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//关闭资源
			try {
				if(rs!=null)
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		//返回结果
		return u;
	}

增删改

	@Override
	public int updatePwd(int uid, String newPwd) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		int x=0;
		//声明变量
		
		try {
			//加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//获取连接
			conn=(Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=utf8","root", "123456");
			//创建sql命令
			String sql="update  user set tel=? where uid=?";
			//创建sql命令对象
			ps=(PreparedStatement) conn.prepareStatement(sql);
			//给占位符赋值
			ps.setString(1, newPwd);
			ps.setInt(2, uid);
			//执行sql
		    x=ps.executeUpdate();

			
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//关闭资源
			try {
				if(rs!=null)
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return x;
		//返回结果
		
	}
``


# 查询结果集

@Override

public List<User> selectAll() {
	// TODO Auto-generated method stub
	List<User> list=new ArrayList<>();
	
	Connection conn=null;
	PreparedStatement ps=null;
	ResultSet rs=null;
	//声明变量
	User u=null;
	try {
		//加载驱动
		Class.forName("com.mysql.jdbc.Driver");
		//获取连接
		conn=(Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=utf8","root", "123456");
		//创建sql命令
		String sql="select * from user ";
		//创建sql命令对象
		ps=(PreparedStatement) conn.prepareStatement(sql);
		//给占位符赋值
	

		//执行sql
		rs=ps.executeQuery();
		//遍历结果
			while(rs.next()){
				//给变量赋值
				u=new User();
				u.setUid(rs.getInt("uid"));
				u.setName(rs.getString("uname"));
				u.setAge(rs.getInt("age"));
				u.setSex(rs.getInt("sex"));				
				u.setTel(rs.getString("tel"));
				list.add(u);
			}
		
	} catch (Exception e) {
		e.printStackTrace();
	}finally{
		//关闭资源
		try {
			if(rs!=null)
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	

	return list;
}