package cn.tedu;
import com.alibaba.druid.pool.DruidDataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DBUtils {
private static DruidDataSource ds;
static {
//通过属性对象读取配置文件数据 替换掉下面写死的内容
//创建属性对象
Properties p=new Properties();
//获取文件输入流
InputStream ips=DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
//让文件和属性对象关联 异常抛出
try {
p.load(ips);
} catch (IOException e) {
e.printStackTrace();
}
//从属性对象中获取数据 只能获取字符串类型
String username=p.getProperty("db.username");
String password=p.getProperty("db.password");
String driver=p.getProperty("db.driver");
String url=p.getProperty("db.url");
//创建数据库连接池
ds=new DruidDataSource();
//设置数据库连接信息
ds.setDriverClassName(driver);
ds.setUrl(url);
ds.setUsername(username);
ds.setPassword(password);
//获取初始数量和最大连接数量
String initSize=p.getProperty("db.initialSize");
String maxSize=p.getProperty("db.maxActive");
ds.setInitialSize(Integer.parseInt(initSize));//设置初始连接数量
ds.setMaxActive(Integer.parseInt(maxSize));//设置最大连接数量
}
public static Connection getConn() throws Exception {
Connection conn=ds.getConnection();//获取连接 异常抛出
System.out.println(conn);
return conn;
}
}
package cn.tedu;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class Demo01 {
public static void main(String[] args) {
try ( Connection conn = DBUtils.getConn()){
Statement s=conn.createStatement();
ResultSet rs=s.executeQuery("select ename,sal from emp where sal>2000");
while (rs.next()){
String name=rs.getString(1);
double sal=rs.getDouble(2);
System.out.println(name+":"+sal);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
package cn.tedu;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
public class Demo02 {
public static void main(String[] args) throws IOException {
//创建属性对象
Properties p=new Properties();
//获取文件输入流
InputStream ips=Demo02.class.getClassLoader().getResourceAsStream("my.properties");
//让文件和属性对象关联 异常抛出
p.load(ips);
//从属性对象中获取数据 只能获取字符串类型
String name=p.getProperty("name");
String age=p.getProperty("age");
System.out.println(name+":"+age);
}
}
package cn.tedu;
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Demo03 {
public static void main(String[] args) throws SQLException {
//创建数据库连接池
DruidDataSource ds=new DruidDataSource();
//设置数据库连接信息
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl("jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true");
ds.setUsername("root");
ds.setPassword("root");
ds.setInitialSize(3);//设置初始连接数量
ds.setMaxActive(5);//设置最大连接数量
Connection conn=ds.getConnection();//获取连接 异常抛出
System.out.println(conn);
}
}
package cn.tedu;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo04 {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户名:");
String username=sc.nextLine();
System.out.println("请输入密码:");
String password=sc.nextLine();
//获取数据库连接
try(Connection conn=DBUtils.getConn()) {
Statement s=conn.createStatement();
String qsql="select id from user where username='"+username+"'";
ResultSet rs=s.executeQuery(qsql);
if(rs.next()){
System.out.println("用户名已存在!");
return;
}
String sql="insert into user values(null,'"+username+"','"+password+"')";
s.executeUpdate(sql);
System.out.println(sql);
System.out.println("执行完成!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
package cn.tedu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo05 {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户名:");
String username=sc.nextLine();
System.out.println("请输入密码:");
String password=sc.nextLine();
//获取连接
try (Connection conn=DBUtils.getConn()){
/* Statement s=conn.createStatement();
String sql="select count(*) from user where username='"+username+"' and password='"+password+"'";
ResultSet rs=s.executeQuery(sql);
*/
//通过预编译SQl执行对象PreparedStatement 解决注入问题
String sql="select count(*) from user where username=? and password=?";
PreparedStatement ps=conn.prepareStatement(sql);//将业务锁死
//替换掉SQl中的?
ps.setString(1,username);
ps.setString(2,password);
ResultSet rs=ps.executeQuery();
//获取结果集中 查询回来的值
rs.next();//让游标指向查询回来的唯一一条数据
int count =rs.getInt(1);
if (count>0){
System.out.println("恭喜您登陆成功!");
}else{
System.out.println("用户名或密码错误!");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
package cn.tedu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class Demo06 {
public static void main(String[] args) {
try (Connection conn=DBUtils.getConn()){
/* String sql1="insert into user values(null,'aaa','aaa')";
String sql2="insert into user values(null,'bbb','bbb')";
String sql3="insert into user values(null,'ccc','ccc')";
Statement s=conn.createStatement();
s.addBatch(sql1);
s.addBatch(sql2);
s.addBatch(sql3);
s.executeBatch();//执行批量操作
*/
//往表里面插入100条数据
String sql="insert into user values(null,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
for (int i = 1; i <=100 ; i++) {
//替换SQL语句中的?
ps.setString(1,"name"+i);
ps.setString(2,"pw"+i);
ps.addBatch();//添加到批量操作
}
ps.executeBatch();//执行批量操作
System.out.println("执行完成!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
package cn.tedu;
import sun.security.pkcs11.Secmod;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
public class Demo07 {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入查询的页数");
int page=sc.nextInt();
System.out.println("请输入查询的条数");
int count=sc.nextInt();
try (Connection conn=DBUtils.getConn()){
//查询出对应的用户名和密码 在控制台输出
String sql="select username,password from user limit ?,?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1,(page-1)*count);
ps.setInt(2,count);
ResultSet rs=ps.executeQuery();
while (rs.next()){
String username =rs.getString(1);
String password =rs.getString(2);
System.out.println(username+":"+password);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
package cn.tedu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo08 {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("用户名");
String username=sc.nextLine();
System.out.println("密码");
String password=sc.nextLine();
try( Connection conn=DBUtils.getConn()) {
String sql="insert into user values(null,?,?)";
PreparedStatement ps=conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1,username);
ps.setString(2,password);
ps.executeUpdate();
System.out.println("执行完毕!");
//获取自增主键值
ResultSet rs=ps.getGeneratedKeys();
rs.next();//游标指向查询出来的数据
int id =rs.getInt(1);
System.out.println("id="+id);
} catch (Exception e) {
e.printStackTrace();
}
}
}
package cn.tedu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo09 {
public static void main(String[] args) {
Scanner sc=new Scanner(System.in);
System.out.println("请输入球队名称");
String teamName=sc.nextLine();
System.out.println("请输入球员名称");
String palyerName=sc.nextLine();
//获取连接
try ( Connection conn=DBUtils.getConn()){
//1.保存球队
String sql="insert into team values(null,?)";
PreparedStatement ps=conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1,teamName);
ps.executeUpdate();
System.out.println("球队保存完成!");
//2.获取球队自增id
ResultSet rs=ps.getGeneratedKeys();
rs.next();
int teamId=rs.getInt(1);
System.out.println("球队id"+teamId);
//3.保存球员
/* sql="insert into player values(null,?,'"+teamId+"')";
ps=conn.prepareStatement(sql);
ps.setString(1,palyerName);
ps.executeUpdate();
System.out.println("球员保存完毕!");*/
String psql="insert into player values(null,?,?)";
PreparedStatement pps=conn.prepareStatement(psql);
pps.setString(1,palyerName);
pps.setInt(2,teamId);
pps.executeUpdate();
System.out.println("球员保存完毕!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
package cn.tedu;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo10 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入球队名称");
String teamName = sc.nextLine();
System.out.println("请输入球员名称");
String palyerName = sc.nextLine();
//获取连接
try (Connection conn = DBUtils.getConn()){
//1. 查询是否有该球队
// 如果有取出球队id,如果没有保存并取出球队id
String sql = "select id from team where name=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,teamName);
ResultSet rs = ps.executeQuery();
//不管之前是否保存过球队 都需获取到球队id
int teamId = 0;
//判断是否查询到了数据
if(rs.next()){//查到了
teamId = rs.getInt(1);
}else{//没有查询到
//把球队保存
String tsql = "insert into team values(null,?)";
PreparedStatement tps = conn.prepareStatement(tsql,
Statement.RETURN_GENERATED_KEYS);
//替换?
tps.setString(1,teamName);
//执行保存球队的SQL
tps.executeUpdate();
System.out.println("球队保存完成!");
//获取自增的球队id
ResultSet trs = tps.getGeneratedKeys();
trs.next();//移动游标
teamId = trs.getInt(1);
}
//2. 保存球员
String psql = "insert into player values(null,?,?)";
PreparedStatement pps = conn.prepareStatement(psql);
pps.setString(1,palyerName);
pps.setInt(2,teamId);
pps.executeUpdate();
System.out.println("保存球员完成!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
package cn.tedu;
import java.sql.*;
public class Demo11 {
public static void main(String[] args) {
//元数据
//数据库元数据:数据库相关的一些信息
//表元数据:表相关的一些信息
//获取连接
try (Connection conn=DBUtils.getConn()){
DatabaseMetaData dbmd=conn.getMetaData();
System.out.println("数据库名称:"+dbmd.getDatabaseProductName());
System.out.println("数据库驱动名称:"+dbmd.getDriverVersion());
System.out.println("数据库连接地址:"+dbmd.getURL());
String sql="select *from emp";
Statement s=conn.createStatement();
ResultSet rs=s.executeQuery(sql);
//获取表相关元数据对象
ResultSetMetaData rsmd=rs.getMetaData();
//获取表字段数量
int count=rsmd.getColumnCount();
//遍历每一个字段信息
for (int i = 0; i <count ; i++) {
//获取字段名
String name=rsmd.getCatalogName(i+1);
//获取字段类型
String type=rsmd.getColumnTypeName(i+1);
System.out.println(name+":"+type);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}