原生jdbc连接数据库
`import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接对象
String url = "jdbc:mysql://39.106.200.227:3306/test?characterEncoding=utf-8";
Connection conn = DriverManager.getConnection(url, "root", "root");
// 3.获取执行SQL语句
Statement stat = conn.createStatement();
// 拼写SQL语句
String sql = "select * from sort";
// 4.调用执行者对象方法,执行SQL语句获取结果集
// 返回的是ResultSet接口的实现类对象,实现类在mysql驱动中
ResultSet rs = stat.executeQuery(sql);
// 4. 执行sql语句
//通过执行者对象调用方法执行SQL语句,获取结果
//int executeUpdate(String sql) 执行数据库中的SQL语句,仅限于insert,update,delete
//返回值int,操作成功数据库的行数
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
int row = stat.executeUpdate("INSERT INTO sort(sname,sprice,sdesc) VALUES('汽车用品',50000,'疯狂涨价')");
System.out.println(row);
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// System.out.println(rs);//com.mysql.jdbc.JDBC4ResultSet@18cef0a
// 5.处理结果集
// ResultSet接口的方法 boolean next() 有结果集true,没有结果集返回false
while (rs.next()) {
// 获取每列的数据,使用的是ResultSet接口的方法getXXX
int sid = rs.getInt("sid");// 相当于rs.getInt(1);这个方法有弊端
String sname = rs.getString("sname");
double sprice = rs.getDouble("sprice");
String sdesc = rs.getString("sdesc");
System.out.println(sid+"\t"+sname+"\t"+sprice+"\t"+sdesc);
}
// 6.关闭资源
rs.close();
stat.close();
conn.close();
}
}`
1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
2.建立连接
String url = "jdbc:mysql://39.106.200.227:3306/test?characterEncoding=utf-8"; Connection conn = DriverManager.getConnection(url, "root", "root");
3创建statement,和数据库打交道一定要有这个对象
Statement stat = conn.createStatement();
4.执行sql
ResultSet rs = stat.executeQuery(sql);
jdbc工具类
package com.stu.util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DbConfig {
/**
* 数据库连接类
*/
private static String username=null;
private static String password=null;
private static String driver=null;
private static String url=null;
static{
Properties ps=new Properties();
try {
ps.load(DbConfig.class.getResourceAsStream("db.properties"));
driver=ps.getProperty("driver");
url=ps.getProperty("url");
username=ps.getProperty("username");
password=ps.getProperty("password");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Connection getCon() throws Exception{
Class.forName(driver);
Connection con=DriverManager.getConnection(url, username, password);
return con;
}
// public static void Close(Connection con)throws Exception{
// if(con!=null){
// con.close();
// }
// }
// public void closeCon(Connection con) {
// // TODO Auto-generated method stub
// if(con!=null){
// try {
// con.close();
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// }
// }
// public void main(String[] args) {
// try {
// System.out.println(getCon());
// } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// }
}
增
//增加
@Override
public int AddDbsupermarket(DbSupermarket dbSupermarket) {
try {
String sql = "insert into dbSupermarket(GNAME,GPRICE) values(?,?)";
Statement stmt = null;
ResultSet rs = null;
DbConfig dbConfig = new DbConfig();
Connection conn = dbConfig.getCon();
PreparedStatement ps= conn.prepareStatement(sql);
//ps.setInt(1,dbSupermarket.getGId());
ps.setString(1,dbSupermarket.getGNAME());
ps.setFloat(2,dbSupermarket.getGPRICE());
ps.executeUpdate();
ps.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return 1;
}
删
//删除
@Override
public int deleteDbsupermarket(int gid) {
try {
DbConfig dbConfig = new DbConfig();
Connection conn = dbConfig.getCon();
String sql = "delete from dbSupermarket where GID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,gid);
ps.executeUpdate();
ps.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return 1;
}
改
//改
@Override
public int updateDbsupermarket(DbSupermarket dbSupermarket) {
try {
DbConfig dbConfig = new DbConfig();
Connection conn = dbConfig.getCon();
String sql = "update dbSupermarket set GNAME = ?,GPRICE = ? where GID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,dbSupermarket.getGNAME());
ps.setFloat(2,dbSupermarket.getGPRICE());
ps.setInt(3,dbSupermarket.getGId());
ps.executeUpdate();
ps.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return 1;
}
查
public List<DbSupermarket> findAll() {
//DbSupermarket dd=new DbSupermarket();
List<DbSupermarket > dbSupermarket= new ArrayList<DbSupermarket>();
try {
String sql = "SELECT * FROM dbSupermarket";
PreparedStatement ps = null;
Statement stmt = null;
ResultSet rs = null;
DbConfig dbConfig = new DbConfig();
Connection conn = dbConfig.getCon();
//stmt = conn.createStatement();
PreparedStatement ps =conn.prepareStatement(sql);
rs = ps.executeQuery(sql);
// System.out.println(rs.getInt("GID"));
while (rs.next()) {
if(rs!=null) {
DbSupermarket dd = new DbSupermarket();
//System.out.println(rs.getInt("GID"));
dd.setGId(rs.getInt("GID"));
dd.setGNAME(rs.getString("GNAME"));
dd.setGPRICE(rs.getFloat("GPRICE"));
dbSupermarket.add(dd);
}
}
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return dbSupermarket;
}
总结
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接对象
String url = "jdbc:mysql://39.106.200.227:3306/test?characterEncoding=utf-8";
Connection conn = DriverManager.getConnection(url, "root", "root");
String sql="select * from sort where like ? or password "
//以上封装成一个方法
PreparedStatement ps =conn.prepareStatement(sql);
ps.setString(1,"")//设置第一个?,模糊查询使用字符串拼接 %+String+%
ReSulSet rs =ps.executeQuery();//实例化ReSulSet对象
rs.next();//指针下移
rs.getString("");//对应什么字段传什么字符串
//关闭连接
rs.close();
ps.close();
conn.close();
//在增删改中ps调用executeUpdate();结果返回更新的记录数
//执行查询数据库操作，ps调用executeQuery();返回一个结果集对象，采用rs接，
注意jdbc在查询操作中，因为是讲数据表中的全部查询结果保存在ResultSet对象中，实际上也就是保存在内存中，所以如果查询出来的数据总量过大，系统将会出现问题
补充
prepareStatement是Statement的子接口，属于预处理操作，和之前操作Statement不同的是，prepareStatement在操作时，是先在数据表之中准备好了一条sql语句，电脑上此sql语句的具体内容暂时不设置，而是之后再设置
