How to lock MySQL tables so only one person can write to file. (Java Programming)

[banging]

Hi there,

I have a question regarding locking of tables so that when two or more people try to write or update the mysql tables, it locks up. Basically I only want one person to write to the file, but many are able to read the files (or tables entities).

I am not sure if I need to lock the tables in my Java code or do I lock the tables within the MySQL syntax. I'm just a little confused on the matter.

This java code is a working prototype of inserting a customer data into the database and that works fine. I just don't know how to implement it so that only one person can update the table at a time.

Here is the Customer.java code that I have written.

Help would be greatly appreciated, thanks so much.

package business;

//~--- non-JDK imports --------------------------------------------------------

import shared.info.CustomerInfo;

//~--- JDK imports ------------------------------------------------------------

import java.sql.*;

/**
*
* @author
*/
public class Customer {
static Connection con = DBConnection.getConnection();

private CustomerInfo info = new CustomerInfo();
private String customerID;
private String firstName;
private String lastName;
private String email;
private String addressID;
private String homePhone;
private String workPhone;
private String unitNum;
private String streetNum;
private String streetName;
private String city;
private String provinceState;
private String country;
private String zipPostalCode;

public Customer(String id) {
try {
PreparedStatement pstmt = con.prepareStatement("SELECT * FROM " +
"Customer NATURAL JOIN Address WHERE CustomerID = ?");

pstmt.setString(1, id);

ResultSet rs = pstmt.executeQuery();

while (rs.next()) {
customerID = rs.getString("CustomerID");
firstName = rs.getString("FirstName");
lastName = rs.getString("LastName");
homePhone = rs.getString("HomePhone");
workPhone = rs.getString("WorkPhone");
email = rs.getString("Email");
city = rs.getString("City");
provinceState = rs.getString("ProvinceState");
country = rs.getString("Country");
zipPostalCode = rs.getString("ZipPostalCode");
unitNum = rs.getString("UnitNum");
streetNum = rs.getString("StreetNum");
streetName = rs.getString("StreetName");
addressID = rs.getString("AddressId");
}
} catch (SQLException e) {
e.printStackTrace();
}

info.setCustomerID(customerID);
info.setFirstName(firstName);
info.setLastName(lastName);
info.setHomePhone(homePhone);
info.setWorkPhone(workPhone);
info.setEmail(email);
info.setCity(city);
info.setProvinceState(provinceState);
info.setCountry(country);
info.setZipPostalCode(zipPostalCode);
info.setUnitNum(unitNum);
info.setStreetNum(streetNum);
info.setStreetName(streetName);
info.setAddressID(addressID);
}

public static void addCustomer(CustomerInfo cust) {
try {
int id = -1;

PreparedStatement pstmt = con.prepareStatement("INSERT INTO Address" +
"(UnitNum, StreetNum, StreetName, City, ProvinceState, Country," +
" ZipPostalCode) VALUES(?, ?, ?, ?, ?, ?, ?)");
pstmt.setString(1, cust.getUnitNum());
pstmt.setString(2, cust.getStreetNum());
pstmt.setString(3, cust.getStreetName());
pstmt.setString(4, cust.getCity());
pstmt.setString(5, cust.getProvinceState());
pstmt.setString(6, cust.getCountry());
pstmt.setString(7, cust.getZipPostalCode());
pstmt.executeUpdate();

ResultSet rs = pstmt.getGeneratedKeys();
rs.next();
id = rs.getInt(1);

pstmt = con.prepareStatement("INSERT INTO Customer" +
"(FirstName, LastName, HomePhone, WorkPhone, Email, AddressID)"
+ "VALUES(?, ?, ?, ?, ?, ?)");
pstmt.setString(1, cust.getFirstName());
pstmt.setString(2, cust.getLastName());
pstmt.setString(3, cust.getHomePhone());
pstmt.setString(4, cust.getWorkPhone());
pstmt.setString(5, cust.getEmail());
pstmt.setInt(6, id);
pstmt.executeUpdate();

} catch (SQLException e) {
e.printStackTrace();
}
}

public void setFirstName(String newName) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Customer"
+ " SET FirstName = ? WHERE CustomerID = ?");

pstmt.setString(1, newName);
pstmt.setString(2, customerID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

firstName = newName;
}

public void setLastName(String newName) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Customer"
+ " SET LastName = ? WHERE CustomerID = ?");

pstmt.setString(1, newName);
pstmt.setString(2, customerID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

lastName = newName;
}

public String getFirstName() {
return firstName;
}

public String getLastName() {
return lastName;
}

public void setHomePhone(String number) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Customer"
+ " SET HomePhone = ? WHERE CustomerID = ?");

pstmt.setString(1, number);
pstmt.setString(2, customerID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

homePhone = number;
}

public String getHomePhone() {
return homePhone;
}

public void setWorkPhone(String number) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Customer"
+ " SET WorkPhone = ? WHERE CustomerID = ?");

pstmt.setString(1, number);
pstmt.setString(2, customerID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

workPhone = number;
}

public String getWorkPhone() {
return workPhone;
}

public void setEmail(String email) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Customer" + " SET Email = ? WHERE CustomerID = ?");

pstmt.setString(1, email);
pstmt.setString(2, customerID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

this.email = email;
}

public String getEmail() {
return email;
}

public void setUnitNum(String num) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Address" + " SET UnitNum = ? WHERE AddressId = ?");

pstmt.setString(1, num);
pstmt.setString(2, addressID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

unitNum = num;
}

public String getUnitNum() {
return unitNum;
}

public void setCity(String city) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Address" + " SET City = ? WHERE AddressId = ?");

pstmt.setString(1, city);
pstmt.setString(2, addressID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

this.city = city;
}

public String getCity() {
return city;
}

public void setStreetNum(String num) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Address" + " SET StreetNum = ? WHERE AddressId = ?");

pstmt.setString(1, num);
pstmt.setString(2, addressID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

streetNum = num;
}

public String getStreetNum() {
return streetNum;
}

public void setStreetName(String name) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Address"
+ " SET StreetName = ? WHERE AddressId = ?");

pstmt.setString(1, name);
pstmt.setString(2, addressID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

streetName = name;
}

public String getStreetName() {
return streetName;
}

public void setZipPostalCode(String code) {
try {
PreparedStatement pstmt = con.prepareStatement("UPDATE Address"
+ " SET ZipPostalCode = ? WHERE AddressId = ?");

pstmt.setString(1, code);
pstmt.setString(2, addressID);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}

zipPostalCode = code;
}

public String getZipPostalCode() {
return zipPostalCode;
}

public CustomerInfo getInfo(){
return info;
}

static void deleteCustomer(String customerId) {
try{
PreparedStatement pstmt = con.prepareStatement("DELETE FROM Customer" +
" WHERE CustomerId = ?");
pstmt.setString(1, customerId);
pstmt.executeUpdate();

}catch(SQLException e){
e.printStackTrace();
}
}

static void updateCustomer(CustomerInfo custInf) {
try{
PreparedStatement pstmt = con.prepareStatement("UPDATE customer" +
" SET firstName = ?, SET lastName = ?," +
" SET homePhone = ?, SET workPhone = ?, SET email = ?" +
" WHERE CustomerId = ?");

pstmt.setString(1, custInf.getFirstName());
pstmt.setString(2, custInf.getLastName());
pstmt.setString(3, custInf.getHomePhone());
pstmt.setString(4, custInf.getWorkPhone());
pstmt.setString(5, custInf.getEmail());
pstmt.setString(6, custInf.getCustomerID());

pstmt.executeUpdate();

pstmt = con.prepareStatement("UPDATE address" +
" SET unitNum = ?, SET StreetNum = ?, SET StreetName = ?," +
" SET city = ?,SET Province = ?,SET country = ?,SET ZipPostalCode = ?" +
" WHERE AddressId = ?");
pstmt.setString(1, custInf.getUnitNum());
pstmt.setString(2, custInf.getStreetNum());
pstmt.setString(3, custInf.getStreetName());
pstmt.setString(4, custInf.getCity());
pstmt.setString(5, custInf.getProvinceState());
pstmt.setString(6, custInf.getCountry());
pstmt.setString(7, custInf.getZipPostalCode());
pstmt.setString(8, custInf.getAddressID());
pstmt.executeUpdate();

}catch(SQLException e){
e.printStackTrace();
}

}

}

In addition, here is my customer sql table.

--
-- Table structure for table `customer`
--

DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` (
`CustomerID` mediumint(9) NOT NULL auto_increment,
`FirstName` varchar(20) NOT NULL,
`LastName` varchar(20) NOT NULL,
`HomePhone` varchar(11) NOT NULL,
`WorkPhone` varchar(11) default NULL,
`Email` varchar(20) NOT NULL,
`AddressID` mediumint(9) default NULL,
PRIMARY KEY (`CustomerID`),
KEY `AddressID` (`AddressID`),
CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`AddressID`) REFERENCES `address` (`AddressID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `customer`
--

LOCK TABLES `customer` WRITE;
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
UNLOCK TABLES;

[N002213F]

using Java code use the synchronized modifier for the method, it should work.

RDBMSes automatically have such locks on tables (records??) to maintain the ACID transactions.