JDBC

jdbc是java提供的一套类和接口

是连接数据库的一套规范 

jdbc操作数据库的步骤

 1.注册驱动  加载驱动类 registerDriver(Driver driver)

 2.获取数据库的连接对象

 getConnection(url,user,password)

  这里就相当于连接上了数据库

 3.通过连接对象 获取sql语句的执行对象

  createStatement()

 4.通过statement对象执行sql语句

  executeUpdate(sql) 返回int  执行DML和DDL

  executeQuery(sql) 返回resultSet   执行DQL

 5.处理执行sql后得到的结果

 6.关闭资源 (close)

                Class.forName("com.mysql.jdbc.Driver");
		String url="jdbc:mysql://localhost:3306/myjdbc01";
		String user="root";
		String password="123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		Statement statement = connection.createStatement();
		String sql="insert into goods "+"(sname,sprice,sdesc)"+"values('手机',2000,'高级手机')";
		int row = statement.executeUpdate(sql);
		System.out.println("行数"+row);
		connection.close();
		statement.close();

返回结果的结果集,获取数据方式1

                String sql="select * from goods";
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			int sid = resultSet.getInt(1);
			String sname = resultSet.getString(2);
			double sprice = resultSet.getDouble(3);
		        String sdesc = resultSet.getString(4);
			System.out.println(sid+sname+sprice+sdesc);}

返回结果的结果集,获取数据方式2

                String sql="select * from goods";
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			String sname = resultSet.getString("sname");
			System.out.println(sname);}

sql注入测试

键盘输入 账号 密码

查询数据库 

并且打印该账号和密码

                System.out.println("请输入账号");
		Scanner scanner1=new Scanner(System.in);
		String zhanghao = scanner1.nextLine();
		System.out.println("请输入密码");
		Scanner scanner2=new Scanner(System.in);
		String mima = scanner2.nextLine();
		Class.forName("com.mysql.jdbc.Driver");
		String url="jdbc:mysql://localhost:3306/myjdbc01";
		String  user="root";
		String  password="123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		Statement statement = connection.createStatement();
		String sql="select * from users where username='"+zhanghao+"' and password='"+mima+"'";
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
		System.out.println(resultSet.getString("username")+""+resultSet.getString("password"));
		}
		connection.close();
		statement.close();
		resultSet.close();

修改数据

用preparestatement  id为1的数据

                Class.forName("com.mysql.jdbc.Driver");
		String url="jdbc:mysql://localhost:3306/myjdbc01";
		String user="root";
		String password="123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		String sql="update goods set sname=?,sprice=? where sid=?";
		PreparedStatement statement = connection.prepareStatement(sql);
		statement.setObject(1, "玩具");
		statement.setObject(2, "10000");
		statement.setObject(3, "1");
		int row  = statement.executeUpdate();
		connection.close();
		statement.close();

重复代码封装成一个类

1.注册驱动

2.获取连接

3.关闭数据库

public class JDBCUtil {
	private static Connection connection;
  public JDBCUtil() {
}
  static {
	  try {
		Class.forName("com.mysql.jdbc.Driver");
	} catch (ClassNotFoundException e) {
		//注册失败没有意义 抛出运行时异常
		throw new RuntimeException("数据库注册失败");
	}
  }
  public static Connection getConnection() {
	  String url="jdbc:mysql://localhost:3306/myjdbc01";
		String user="root";
		String password="123456";
		try {
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			throw new RuntimeException("数据库连接失败");
		}
		return connection;
}
   public static void close(Connection connection,PreparedStatement statement,ResultSet resultSet) {
	   if (connection!=null) {
		try {
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	   if (statement!=null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	   if (resultSet!=null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
}

查询goods表

并且将查出来的数据保存到对象中 

封装一个方法 需要数据库中的数据作为参数

并且放入集合中

遍历数据

Connection connection = JDBCUtil.getConnection();
		String sql="select * from goods";
		PreparedStatement statement = connection.prepareStatement(sql);
		ResultSet resultSet = statement.executeQuery();
		ArrayList<Goods> list =new ArrayList<>();
		while (resultSet.next()) {
			Goods g=new Goods(resultSet.getInt("sid"), resultSet.getString("sname"),
					resultSet.getDouble("sprice"), resultSet.getString("sdesc"));
			list.add(g);	
		}
		JDBCUtil.close(connection, statement, resultSet);
		for (Goods goods : list) {
			System.out.println(goods);
		}