Ping MySQL Server Using JDBC

This seems rather simple (not for me)...

How does one ping a mysql server using the JDBC? i have successfully used select and insert queries with JDBC and MySQL, but how to ping?开发者_开发百科??

TIA

---

The MySQL JDBC driver (Connector/J) provides a ping mechanism.

If you do a SQL query prepended with /* ping */ such as:

"/* ping */ SELECT 1"

This will actually cause the driver send a ping to the server and return a fake, light-weight, result set.

(You can find this buried fairly deep in the Connector/J documentation; search for "ping" on that page. Read it carefully: this mechanism is very sensitive to the syntax used. Unlike most SQL, the "parsing" of the "ping" marker happens in the client-side JDBC driver itself.).

---

I don't think there is anything in the JDBC APi for this.

But you can simply use InetAddress and the isReachable() method to check if the server is there (which is essentially what a ping is doing).

http://download.oracle.com/javase/6/docs/api/java/net/InetAddress.html#isReachable%28int%29

Edit:
If you want to check if MySQL is running rather than pinging the server you could try to open a socket to the server on the MySQL default port.

Something like this:

InetAddress addr = InetAddress.getByName("your.server.com");
int port = 3306;
SocketAddress sockaddr = new InetSocketAddress(addr, port);
Socket sock = new Socket();
sock.connect(sockaddr, 2000); // open the connection with a 2 seconds timeout

If connect() does not throw an exception something is running on port 3306.

If you want to make sure that it's a MySQL server on that port, the only way is to establish a JDBC connection (but you need a valid username/password for that) and run a statement e.g. the PING statement mentioned by Brian.

---

I don't know how to do this in JDBC by you can use Connection Pooling to validate your connection like so (This is the context.xml for Tomcat (found in the META-INF directory of your application)):

<Resource name="jdbc/My_DS" auth="Container" type="javax.sql.DataSource"
            maxActive="100" maxIdle="30" maxWait="10000"
            username="USERNAME" password="PASSWORD" driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/MYDB?autoReconnect=true" 
            removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
            testOnBorrow="true" validationQuery="SELECT 1" />

The validationQuery validates if the connection is successful or not.