连接Mysql数据库

时间：2021-09-30 04:22:30

数据库学的特别渣，期末考试都是蒙过的连接Mysql数据库，哈哈

现在正好可以好好的复（yu）习一下，数据库知识特别的重要，任何程序，网页不连数据库，都是废品一个

数据库的知识必须学好，啰嗦了这么多，来咱们一起回顾一下吧

1.首先安装mysql,去官网下载就可以

2.导包，将mysql的驱动包复制到WebContent/WEB-INF/lib目录下

     3.开始写代码

（1）.加载jdbc驱动类

Class.forName(com.mysql.jdbc.Driver);

（2）.建立数据库连接

Connnection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名"

"+"?user=root&password = root&useUnicode=true&characterEncoding=utf-8"));

（3）.创建PreparedStatement对象

String sql="select * from books";

PreparedStatement pst =conn.preparedStatement(sql);

以下是查询操作，其实增删改查都一样，只是sql语句不同

ResultSet rs=pst.executeQuery();
while(rs.next()){
System.out.println(rs.getString("isbn") + " "
+ rs.getString("bookName") + " "
+ rs.getInt("publisherId") + " "
+ rs.getDouble("price") + " " + rs.getInt("count"));
}

//关闭
rs.close();
pst.close();
conn.close();

好了，应该连接好了

package ch_01;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class test1{
	Scanner sc=new Scanner(System.in);
	/**
	 * 图书管理界面
	 * 
	 * @param sc
	 */
	private void View() {
		int id;
		System.out.println("\t\t\t------------------欢迎来到图书管理界面------------------");
		System.out.println("\t\t\t------------------1、查看图书信息------------------");
		System.out.println("\t\t\t------------------2、添加图书信息------------------");
		System.out.println("\t\t\t------------------3、修改图书信息------------------");
		System.out.println("\t\t\t------------------4、删除图书信息------------------");
		System.out.println("\t\t\t------------------5、退出------------------");
		System.out.print("请输入要进行的操作:");
		String operate=sc.next();
		switch(operate){
		case "1":
			//调用UserServiceImpl类中的searchUser(sc)查询用户信息;
			searchBook();
			//查询完毕后再返回用户管理界面，重新选择其他操作，递归调用showUserView(sc)方法。
			View();
			break;
		case "2":
			//调用UserServiceImpl类中的addUser(sc)添加用户信息;
			addBook();
			//查询完毕后再返回用户管理界面，重新选择其他操作，递归调用showUserView(sc)方法。
			View();
			break;
		case "3":
			System.out.println("请输入要修改的图书编号：");
			Scanner sd = new Scanner(System.in);
			updateBook(sd.next());
			View();
			break;
		case "4":
			System.out.println("请输入要删除的图书编号：");
			Scanner s2d = new Scanner(System.in);
			deleteBook(s2d.next());
			View();
			break;
		case "5":
			//返回到调用它的上一级showAdminView(sc)方法
			System.out.println("您已退出系统！");
			System.exit(0);
			break;	
		default:
			System.out.println("输入的操作符不正确，请重新输入：");
			View();			
		}

	}
	private void deleteBook(String isbn) {
		try {
			//加载jdbc驱动类
			Class.forName("com.mysql.jdbc.Driver");
			//建立数据库连接
			Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/students"
					+"?user=root&password=root&useUnicode=true"+
					"&characterEncoding=utf-8");
			//创建Statement/PreparedStatement对象
//			String sql="delete * from books where isbn=?";
//			PreparedStatement pst=conn.prepareStatement(sql);
//			pst.setInt(1,id);
			
			String sql="delete from books where isbn=?";
			PreparedStatement pst = conn.prepareStatement(sql);
			//设置动态参数
			pst.setString(1,isbn);
			int x=pst.executeUpdate();
			//处理结果
			if(x>0){
				System.out.println("成功删除！");
			}
			else{
				System.out.println("删除失败！ ");
			}
			
			//关闭

			pst.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private void updateBook(String isbn) {
		try {
			//加载jdbc驱动类
			Class.forName("com.mysql.jdbc.Driver");
			//建立数据库连接
			Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/students"
					+"?user=root&password=root&useUnicode=true"+
					"&characterEncoding=utf-8");
//实现更新
			String sql="update books set bookname=?,publisherid=?,price=?,count=? "
					+ "where isbn=?";
			//创建Statement/PreparedStatement对象
			PreparedStatement pst = conn.prepareStatement(sql);
			//输入修改后的值
			Scanner s=new Scanner(System.in);
			System.out.println("请输入bookname：");
			String bookname=s.next();
			System.out.println("请输入publisherid：");
			int publisherid=s.nextInt();
			System.out.println("请输入price：");
			Double price=s.nextDouble();
			System.out.println("请输入count：");
			int count=s.nextInt();
			//设置动态参数
			pst.setString(1,bookname);
			pst.setInt(2,publisherid);
			pst.setDouble(3, price);
			pst.setInt(4,count);
			pst.setString(5,isbn);
//			pst.setString(1, "CCC");
//			pst.setInt(2,123123);
//			pst.setDouble(3,23.4);
//			pst.setInt(4, 23);
//			pst.setString(5,isbn);
			int x=pst.executeUpdate();
			//处理结果
			if(x>0){
				System.out.println("成功修改！");
			}
			else{
				System.out.println("修改失败！ ");
			}
			
			//关闭

			pst.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private void addBook() {
		try {
			//加载jdbc驱动类
			Class.forName("com.mysql.jdbc.Driver");
			//建立数据库连接
			Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/students"
					+"?user=root&password=root&useUnicode=true"+
					"&characterEncoding=utf-8");
			//创建Statement/PreparedStatement对象
		//	String sql="insert into userdetail(username) values=(?)";
			
			String sql="insert into books(isbn,bookname,publisherid,price,"
					+ "count) values(?,?,?,?,?)";

			PreparedStatement pst=conn.prepareStatement(sql);
			Scanner s=new Scanner(System.in);
			System.out.println("请输入isbn：");
			String isbn=s.next();
			System.out.println("请输入bookname：");
			String bookname=s.next();
			System.out.println("请输入publisherid：");
			int publisherid=s.nextInt();
			System.out.println("请输入price：");
			Double price=s.nextDouble();
			System.out.println("请输入count：");
			int count=s.nextInt();
			//设置动态参数
			pst.setString(1,isbn);
			pst.setString(2,bookname);
			pst.setInt(3,publisherid);
			pst.setDouble(4, price);
			pst.setInt(5,count);
			
			//pst.setString(1,"a1452dmin");
			int x=pst.executeUpdate();
			//处理结果
			if(x>0){
				System.out.println("成功插入！");
			}
			else{
				System.out.println("插入失败！ ");
			}
			
			pst.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
				
	}
	private void searchBook() {
				try {
			//加载jdbc驱动类
			Class.forName("com.mysql.jdbc.Driver");
			//建立数据库连接
			Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/students"
					+"?user=root&password=root&useUnicode=true"+
					"&characterEncoding=utf-8");
			//创建PreparedStatement对象
			String sql="select * from books";
			PreparedStatement pst=conn.prepareStatement(sql);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				 System.out.println(rs.getString("isbn") + " "
				 + rs.getString("bookName") + " "
				 + rs.getInt("publisherId") + " "
				 + rs.getDouble("price") + " " + rs.getInt("count"));
			}
			
			//关闭
			rs.close();
			pst.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private boolean searchBookById(int id){
		boolean flag=false;
		
		return flag;
	}
	public static void main(String[] args) {
		new test1().View();
	}

}

标签：

相关文章