JDBC

1

//import java.sql.Connection;
//import java.sql.DriverManager;
import java.sql.*;
//import javax.sql.ConnectionEvent;

//import com.mysql.jdbc.Driver;

/* 步骤
1.	注册驱动.
	告知jvm使用的是哪一个数据库的驱动
2.	获得连接.
	使用jdbc中的类 完成对MySQL数据库的连接
3.	获得语句执行平台
	通过连接对象获取对sql语句的执行者对象
4.	执行sql语句
	使用执行者对象，向数据库执行sql语句
	获取数据库执行后的结果
5.	处理结果
6.	释放资源.
	一堆close()
 */
public class JDBCDemo {
	public static void main(String[] args) throws ClassNotFoundException,Exception {
		//1.注册驱动
		//DriverManager.registerDriver(new Driver());
		Class.forName("com.mysql.jdbc.Driver");
		//2.获得数据库的连接
		//static Connection getConnection(String url,String user,String password)
		//DriverManager类中的静态方法 getConnection
		//url :数据库地址 jdbc:mysql://连接主机ip：端口号//数据库的名字
		String url = "jdbc:mysql://localhost:3306/mybase";
		String username ="root";
		String password = "123";
		Connection con = DriverManager.getConnection(url,username,password);
		//System.out.println(con);
		//3.获取sql语句  con对象调用方法
		Statement stat = con.createStatement();
		//System.out.println(stat);
		//4.执行sql语句
		//int executeUpdate(String sql) 执行数据库中的sql语句  insert delete update
		//返回值是执行行数
		int row = stat.executeUpdate
				("INSERT INTO sort(sname,sprice,sdesc) VALUES('电风扇',200,'不要钱')");
		System.out.println(row);
		//6释放资源
		stat.close();
		con.close();
	}
}

查找

import java.sql.*;


public class JDBCDemo2 {
	public static void main(String[] args) throws Exception {
	//1.注册
	Class.forName("com.mysql.jdbc.Driver");
	//2.连接对象
	String url = "jdbc:mysql://localhost:3306/mybase";
	String username ="root";
	String password = "123";
	Connection con = DriverManager.getConnection(url,username,password);
	//3.获取执行者
	Statement stat = con.createStatement();
	//拼写查询的SQL
	String sql = "SELECT * FROM sort;";
	//4.调用执行者对象的方法，获取结果
	//ResultSet executeQuery(String sql) 执行sql语句中的select查询
	//返回值 ResuleSet接口的实现类对象
	ResultSet rs = stat.executeQuery(sql);
	//5.处理结果集
	//System.out.println(rs);
	//ResultSet接口方法boolean next() 返回true 有结果集 false 没有结果集
	while(rs.next()) {
		System.out.println(rs.getInt("sid")+"  "+rs.getString("sname")+"  "
		+rs.getDouble("sprice")+"  "+rs.getString("sdesc"));
	}
	rs.close();
	stat.close();
	con.close();
	}
}

登陆 防止注入攻击

import java.sql.*;
import java.util.*;


/*
  用java实现用户登陆
  
  防止注入攻击
  PrepaaredStatement() 
 */
public class JDBCDemo4 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mybase";
		String username ="root";
		String password = "123";
		Connection con = DriverManager.getConnection(url,username,password);
		
		Scanner sc = new Scanner(System.in);
		String user = sc.nextLine();
		String pass = sc.nextLine();
		
		String sql = "SELECT * FROM users WHERE username=? AND uspass=?";
		//调用Connection接口的prepaaredStatement方法，获取prepaaredStatement接口的实现类
		PreparedStatement pst = con.prepareStatement(sql);
		//调用pst对象的set方法，设置？上的参数
		pst.setObject(1, user);
		pst.setObject(2, pass);
		
		ResultSet rs = pst.executeQuery();
		if(rs.next())
			System.out.println("登陆成功");
		else
			System.out.println("登陆失败");
		rs.close();
		pst.close();
		con.close();
		sc.close();
	}
}

修改数据

import java.sql.*;


/*
修改数据

*/
public class JDBCDemo5 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mybase";
		String username ="root";
		String password = "123";
		Connection con = DriverManager.getConnection(url,username,password);
		
		
		//拼写sql语句
		String sql = "UPDATE sort SET sname=?,sprice=? where sid=?";
		//调用set方法设置？占位符
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setObject(1, "汽车美容");
		pst.setObject(2, 465465);
		pst.setObject(3, 7);
		//调用pst方法执行sql语句
		pst.executeUpdate();
	
		pst.close();
		con.close();
	}
}

查询

import java.sql.*;

/*
 * 实现查询
 * */
public class JDBCDemo6 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mybase";
		String username ="root";
		String password = "123";
		Connection con = DriverManager.getConnection(url,username,password);
		
		String sql = "SELECT * FROM sort";
		PreparedStatement pst = con.prepareStatement(sql);
		//调用pst对象的方法，执行查询语句
		ResultSet rs = pst.executeQuery();
		while(rs.next()) {
			System.out.println(rs.getString("sid")+"  "+rs.getString("sname")
			+"  "+rs.getString("sprice")+"  "+rs.getString("sdesc"));
			
		}
		rs.close();
		pst.close();
		con.close();
	}	
}

用配置文件方式建立与数据库的连接

配置信息

Driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybase
username=root
password=123

import java.io.*;
import java.util.Properties;
import java.sql.*;

/*
 * 加载properties配置文件
 * IO读取文件 键值对存储到集合
 * 从集合中以键值对方式获取数据库的连接信息 完成数据库的连接
 * 
 * */
public class PropertiesDemo {
	public static void main(String[] args) throws Exception {
		//类加载器
		InputStream in = PropertiesDemo.class.getClassLoader().getResourceAsStream("Database.properties");
		System.out.println(in);
		Properties pro = new Properties();
		pro.load(in);
		//获取集合中的键值对
		String driverClass = pro.getProperty("Driver");
		String url = pro.getProperty("url");
		String username = pro.getProperty("username");
		String password = pro.getProperty("password");
		Class.forName(driverClass);
		Connection con = DriverManager.getConnection(url,username,password);
		System.out.println(con);
		
	}
}

这是 干嘛。。。

package demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.itcast.domain.Sort;

public class JDBCDemo {
	public static void main(String[] args) throws Exception {
		//使用jdbc工具类 直接获取数据库连接对象
		Connection con =JDBCutil.getConnection();
		//连接获取sql语句执行者对象
		PreparedStatement pst = con.prepareStatement("SELECT * FROM sort");
		//调用查询方法，获取结果集
		ResultSet rs = pst.executeQuery();
		//创建集合对象
		List<Sort> list = new ArrayList<Sort>();
		while(rs.next()) {
			//获取刀的每个列对象 都封装到sort对象中
			Sort s = new Sort(rs.getInt("sid"),rs.getString("sname"),rs.getDouble("sprice"),rs.getString("sdesc"));
			//封装sort对象，储存刀集合中
			list.add(s);				
		}
		for(Sort s : list) {
			System.out.println(s);
		}
		JDBCutil.close(con, pst, rs);
	}
}

2

package cn.itcast.domain;

public class Sort {
	private int sid;
	private String sname;
	private double sprice;
	private String sdesc;
	public Sort(int sid, String sname,double sprice, String sdesc) {
		this.sid = sid;
		this.sname = sname;
		this.sprice = sprice;
		this.sdesc = sdesc;	
	}
	public Sort() {}
	public int getSid() {
		return sid;
	}
	public void setSid(int sid) {
		this.sid = sid;
	}
	public String getSname() {
		return sname;
	}
	public void setSname(String sname) {
		this.sname = sname;
	}
	public double getSprice() {
		return sprice;
	}
	public void setSprice(double sprice) {
		this.sprice = sprice;
	}
	public String getSdesc() {
		return sdesc;
	}
	public void setSdesc(String sdesc) {
		this.sdesc = sdesc;
	}
	@Override
	public String toString() {
		return "Sort [sid=" + sid + ", sname=" + sname + ", sprice=" + sprice + ", sdesc=" + sdesc + "]";
	}
}

这又是干嘛

package Test;

import java.sql.Connection;

import demo.JDBCutil;

public class Test {
	public static void main(String[] args) {
		Connection con = JDBCutil.getConnection();
		System.out.println(con);
	}
}

1

package demo;
import java.sql.*;

/*
 * 实现jdbc的工具类
 * 定义一个方法 返回数据库的连接对象
 * */
public class JDBCutil {
	private JDBCutil() {}
	private static Connection con;
	static {
		try {
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/mybase";
		String username ="root";
		String password = "123";
		con = DriverManager.getConnection(url,username,password);
		}catch(Exception e) {
			throw new RuntimeException(e+"连接失败");
		}
	}
	public static Connection getConnection() {
		return con;
	}
	public static void close(Connection con,Statement stat,ResultSet rs) {
		if(rs!=null) {
			try {
				rs.close();
			}catch(SQLException e) {}			
		}
		if(con!=null) {
			try {
				con.close();
			}catch(SQLException e) {}			
		}
		if(stat!=null) {
			try {
				stat.close();
			}catch(SQLException e) {}			
		}		
	}
}

2

package demo;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class JDBCutil2 {
	private static Connection con;
	private static String driverClass;
	private static String url;
	private static String username;
	private static String password;
	
	static {
		try {
		readConfin();
		Class.forName(driverClass);
		con = DriverManager.getConnection(url,username,password);
		}catch(Exception e) {
			throw new RuntimeException("数据库连接失败");
		}
		
	}
	public static void readConfin () throws Exception{
		InputStream in = JDBCutil2.class.getClassLoader().getResourceAsStream("Database.properties");
		Properties pro = new Properties();
		pro.load(in);
		//获取集合中的键值对
		driverClass = pro.getProperty("Driver");
		url = pro.getProperty("url");
		username = pro.getProperty("username");
		password = pro.getProperty("password");
	}
	public static Connection getConnection() {
		return con;
	}
	public static void main(String[] args) {
		
	}
}