JDBC的开发步骤
public class Main {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// write your code here
//1.加载mysql数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接对象
String url = "jdbc:mysql://localhost:3306/mybase";//要连接的数据库服务器的基本信息，包括ip,端口，数据库名
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,username,password);
//System.out.println(connection);
//3.创建数据库执行对象，相当于navicat里的查询窗口
Statement statement = connection.createStatement();
//4.执行查询语句
ResultSet resultSet = statement.executeQuery("select * from sort");
while(resultSet.next())//等同于while(resultSet.next() = true)
{
//System.out.println(resultSet.getString("sname"));//resultSet.getX("Y")Y属于什么类型，X就是什么类型
String strName = resultSet.getString("sname");
Double dPrice = resultSet.getDouble("sprice");
System.out.println(strName + " " + dPrice);
}
//插入一条记录
//int rows = statement.executeUpdate("insert into sort(sname,sprice,sdesc) values('汽车用品',50000,'疯狂涨价')");
//删除一条记录
//int delRows = statement.executeUpdate("delete from sort where sid = 1");
//5.关闭数据库连接
resultSet.close();//需要先关闭
statement.close();
connection.close();
}
}
注入攻击(不用这个代码编程序)
public class LoginDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载mysql数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接对象
String url = "jdbc:mysql://localhost:3306/mybase";//要连接的数据库服务器的基本信息，包括ip,端口，数据库名
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,username,password);
//3.创建数据库执行对象，相当于navicat里的查询窗口
Statement statement = connection.createStatement();
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String uName = scanner.nextLine();
System.out.println("请输入密码：");
String pwd = scanner.nextLine();
String strSql = "select * from users where username = '"+uName+"' and password = '"+pwd+"' ";
//4.处理登录
ResultSet resultSet = statement.executeQuery(strSql);
if (resultSet.next() == false)
{
System.out.println("登录失败");
}
else
{
System.out.println("登录成功");
}
//5.关闭连接
resultSet.close();
statement.close();
connection.close();
}
}
(用这个代码编程序)
public class LoginDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载mysql数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接对象
String url = "jdbc:mysql://localhost:3306/mybase";//要连接的数据库服务器的基本信息，包括ip,端口，数据库名
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,username,password);
//3.创建数据库执行对象，相当于navicat里的查询窗口
Scanner scanner = new Scanner(System.in);
System.out.println("请输入用户名：");
String uName = scanner.nextLine();
System.out.println("请输入密码：");
String pwd = scanner.nextLine();
PreparedStatement preparedStatement = connection.prepareStatement("select * from users where username = ? and password = ?");
preparedStatement.setString(1,uName);
preparedStatement.setString(2,pwd);
//4.处理登录
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next() == false)
{
System.out.println("登录失败");
}
else
{
System.out.println("登录成功");
}
//5.关闭连接
resultSet.close();
preparedStatement.close();
connection.close();
}
}
更新操作
public class UpdateDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载mysql数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接对象
String url = "jdbc:mysql://localhost:3306/mybase";//要连接的数据库服务器的基本信息，包括ip,端口，数据库名
String username = "root";
String password = "root";
Connection connection = DriverManager.getConnection(url,username,password);
//3.创建数据库执行对象，相当于navicat里的查询窗口
PreparedStatement preparedStatement = connection.prepareStatement("update sort set sname = ? ,sprice = ?, sdesc=? where sid = ? ");
preparedStatement.setString(1,"口红");
preparedStatement.setDouble(2,11000.1);
preparedStatement.setString(3,"限量版口红");
preparedStatement.setInt(4,4);
//4.处理(执行)
int rows = preparedStatement.executeUpdate();
//5.关闭数据库连接
preparedStatement.close();
connection.close();
}
}
JDBC的工具类(代码固定)
public class JDBCUtils {
private static Connection conn = null;
private static String url;
private static String username;
private static String password;
static {
InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
try {
properties.load(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
//1.加载mysql数据库驱动
try {
Class.forName(properties.getProperty("driver"));
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2.获得数据库连接对象
url = properties.getProperty("url");
//要连接的数据库服务器的基本信息，包括（ip，端口，数据库名）
username = properties.getProperty("username");
password = properties.getProperty("password");
}
private JDBCUtils(){}
public static Connection getConnection()
{
try {
conn = DriverManager.getConnection(url,username,password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(Statement statement, Connection connection)
{
if(statement != null)
{
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null)
{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet resultSet, Statement statement, Connection connection)
{
if(resultSet != null)
{
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null)
{
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null)
{
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
数据表数据存储对象
public class Sort {
private int sid;
private String sname;
private double sprice;
private String sdesc;
private Date sdate;
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;
}
public Date getSdate() {
return sdate;
}
public void setSdate(Date sdate) {
this.sdate = sdate;
}
@Override
public String toString() {
return "Sort{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", sprice=" + sprice +
", sdesc='" + sdesc + '\'' +
", sdate=" + sdate +
'}';
}
}
//增加
public static int AddSort(Sort sort) throws SQLException {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("insert into sort(sname,sprice,sdesc,sdate) values(?,?,?,?)");
preparedStatement.setString(1,sort.getSname());
preparedStatement.setDouble(2,sort.getSprice());
preparedStatement.setString(3,sort.getSdesc());
//java.util.date--->java.sql.date
Date date = sort.getSdate();
//得到date对应的时间戳
long timestamp = date.getTime();
//通过时间戳创建java.sql.Date类型的对象
java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);
preparedStatement.setTimestamp(4,sqlDate);
int row = preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement,connection);
return row;
//增加
public static void main(String[] args) throws SQLException {
System.out.println("增加");
Sort sort = new Sort();
sort.setSname("小饰品");
sort.setSprice(900);
sort.setSdesc("好看");
Date date = new Date();
sort.setSdate(date);
AddSort(sort);
}
//删除
public static int deleteSortByID(int id) throws SQLException {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("delete from sort where sid = ?");
preparedStatement.setInt(1,id);
int row = preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement,connection);
return row;
}
//删除
public static void main(String[] args) throws SQLException {
System.out.println("删除");
deleteSortByID(4);
}
//更新(改)
public static int updateSortByID(Sort sort) throws SQLException {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("update sort set sname = ?,sprice = ?, sdesc=?,sdate = ? where sid = ?");
preparedStatement.setString(1,sort.getSname());
preparedStatement.setDouble(2,sort.getSprice());
preparedStatement.setString(3,sort.getSdesc());
Date date = sort.getSdate();
//得到date对应的时间戳
long timestamp = date.getTime();
//通过时间戳创建java.sql.Date类型的对象
java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);
preparedStatement.setTimestamp(4,sqlDate);
preparedStatement.setInt(5,sort.getSid());
int row = preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement,connection);
return row;
}
//更新
public static void main(String[] args) throws SQLException {
System.out.println("更新");
Sort sort = new Sort();
sort.setSname("芭比娃娃");
sort.setSprice(1400);
sort.setSdesc("好看");
Date date = new Date();
sort.setSdate(date);
sort.setSid(9);
updateSortByID(sort);
}
//查询——查一个
public static Sort getSortByID(int id) throws SQLException {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from sort where sid = ?");
preparedStatement.setInt(1,id);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next() == true)
{
int sid = resultSet.getInt("sid");
String strName = resultSet.getString("sname");
double dPrice = resultSet.getDouble("sprice");
String sdesc = resultSet.getString("sdesc");
Sort sort = new Sort();
sort.setSid(sid);
sort.setSname(strName);
sort.setSprice(dPrice);
sort.setSdesc(sdesc);
return sort;
}
else
{
return null;
}
}
//查询——查全部
public static List<Sort> getAllSort() throws SQLException {
Connection connection = JDBCUtils.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement("select * from sort");
List<Sort> sortList = new ArrayList<>();
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next() == true)
{
int sid = resultSet.getInt("sid");
String strName = resultSet.getString("sname");
double dPrice = resultSet.getDouble("sprice");
String sdesc = resultSet.getString("sdesc");
Date date = resultSet.getTimestamp("sdate");
Sort sort = new Sort();
sort.setSid(sid);
sort.setSname(strName);
sort.setSprice(dPrice);
sort.setSdesc(sdesc);
sort.setSdate(date);
sortList.add(sort);
}
return sortList;
}
//查询
public static void main(String[] args) throws SQLException {
//查一个
System.out.println("查一个");
Sort sort = getSortByID(2);
if(sort != null)
{
System.out.println(sort);
}
else
{
System.out.println("不存在");
}
//查全部
System.out.println("查全部");
List<Sort> sortList = getAllSort();
for (Sort sort1: sortList)
{
System.out.println(sort1);
}
db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybase?useUnicode=true&characterEncoding=UTF-8
username=root
password=root
java.util.date转为java.sql.date
Date date = sort.getSdate();
//得到date对应时间戳
long timestamp = date.getTime();
//通过时间戳创建java.sql.Date类型的对象
java.sql.Date sqiDate = new java.sql.Date(timestamp);
