package com.Jdbc.demo;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import com.mysql.jdbc.Connection;
public class jdbc02 {
public static final String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=UTF-8"; //连接数据库的URL地址
public static String username = "root"; //数据库的用户名
public static String password = ""; //数据库的密码
public static Connection conn=null;//连接对象
public static Statement stmt=null;//语句
public static ResultSet rs = null;//结果集
//1.加载MySQL数据库驱动
static
{
try {
Class.forName("com.mysql.jdbc.Driver");
//2、建立数据库连接
conn = (Connection) DriverManager.getConnection(url,username,password);
if(conn != null)
{
System.out.println("数据库连接正常");
}
else
{
System.out.println("数据库连接失败");
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
//查询所有的学生资料
public static void query()
{
String sql = "select * from students;";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while(rs.next())
{
System.out.println("学号："+rs.getInt("sid")+",姓名："+rs.getString("sname")+",年龄:"+rs.getInt("age")+",性别："+rs.getString("gender"));
}
} catch (Exception e)
{
e.printStackTrace();
}
finally
{
destoryResource();
}
}
//添加学生方法
public static boolean add()
{
String sql = "insert into Students values (11,'张三天',138,'f','zhangsan@qq.com','广州阳江');";
try
{
stmt = conn.createStatement();
int result = stmt.executeUpdate(sql);
if(result > 0)
{
System.out.println("数据添加成功");
return true;
}
else
{
System.out.println("数据库添加失败");
return false;
}
}
catch(Exception ex)
{
ex.printStackTrace();
return false;
}
finally
{
destoryResource();
}
}
//释放资源的方法
public static void destoryResource()
{
try {
if(rs != null)
{
rs.close();
rs = null;
}
if (stmt != null)
{
stmt.close();
stmt = null;
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
//释放最后资源
public static void destoryallResource()
{
try
{
if (conn != null)
{
conn.close();
conn = null;
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
//删除指定学号的学生资料
public static boolean delete(int sid)
{
String sql = "delete from students where sid="+sid;
try
{
stmt = conn.createStatement();
int result = stmt.executeUpdate(sql);
if(result>0)
{
System.out.println("数据添删除成功");
return true;
}
else
{
System.out.println("数据添没有删除");
return false;
}
}
catch(Exception ex)
{
ex.printStackTrace();
return false;
}
finally
{
destoryResource();
}
}
//修改所有学生的年龄为20岁
public static boolean update(int age)
{
String sql = "update students set age="+age;
try
{
stmt = conn.createStatement();
int result = stmt.executeUpdate(sql);
if(result>0)
{
return true;
}
else
{
return false;
}
}
catch(Exception ex)
{
ex.printStackTrace();
return false;
}
finally
{
destoryResource();
}
}
public static void main(String[] args)
{
jdbc02.query(); //查询语句
if (jdbc02.add())
{
System.out.println("添加成功！");
}
else
{
System.out.println("添加失败！");
}
System.out.println("---------------------");
jdbc02.query();
jdbc02.delete(11);
System.out.println("------删除学号为11的学生之后--------");
jdbc02.query();
jdbc02.update(20);
System.out.println("------修改所有学生年龄为20岁--------");
jdbc02.query();
jdbc02.destoryallResource(); //释放资源
}
}